Hope you can shed some light on Sales Costs (Actual) and Sales Excise (Actual).
I have done some investigation to trace where Sales Costs (Actual) and Sales Excise (Actual) derive from.
From the DW (Posted Sales Transactions) I can see that Sales Costs (Actual) comes from the Nav_dbo_Value Entry table in JetNavStage area as a custom field and Sales Excise (Actual) is also a custom field.
When I drill down further I have noticed that this columns is located in the Jet_Historic Posted Sales view in the NAV data source (see 2nd screenshot from SQL) as this 2 fields are not listed in the source table (Value Entry). If my judgement is correct and you more than welcome to correct me if I am wrong, the Sales Costs (Actual) and Sales Excise (Actual) gets created manually in the Nav_dbo_Value Entry table as custom fields and retrieves its data from the Jet_Historic Posted sales automatically when the DW gets updated in the evenings. This also goes for the Sales Revenue (Actual)
What I am trying to do is , is to create calculations in Tableau for my visualizations, however as stated above the columns mentioned is not in the Value Entry source table in NAV, for me to create the calculations as per the calculations on the cube measures. I do not connect Tableau to the cubes , I connect directly to the source tables in NAV. The columns is in my fact table in the data warehouse because it gets it from the staging area when the ETL process runs.
For example: If I want to do a calculation on the live data to calculate the Gross Revenue , I will have to have Sales Revenue and Sales Excise
Because Sales Revenue – Sales Excise = Gross Revenue
What I have been trying to do is , join the Jet_Historic Posted Sales Transactions view to the Value Entry Table so that I can retrieve the data for that columns, however the query runs very long.
Do you have any suggestions or ideas you can share based on my explanation
Thank you in advance
