Please I need help with some price analysis in QlikSense; I have an InvoiceSales table with product,quantitysold,actualrevenue and product category fields, and a SuggestedRates table with category and monthly rates.
My goal is to calculate the 'Estimated Revenue' and 'Revenue Variance' based on the suggested rates for the month selected(see attached sample qvf). So if Jan-16 is selected, 'Estimated Revenue' = (QuantitySold*$0.620) for Paper products, (QuantitySold*0.655) for Plastic products.
How can I achieve this? Is it possible to declare a variable for each month with multiple rates?
Thanks Clever/Greg! The actual dataset is a lot larger than the sample and it's updated monthly with new rates so I suppose the CrossTable function would work for organizing the data.
That said, after the data reorg how do I go about calculating the 'Estimated Revenue' and 'Revenue Variance'?
With the data exposed as a column, that column can now be used as a dimension, so you could create a chart object with the dimensions of 'RefDate', 'Product', and 'Category' (order as you see fit). Set your expressions to be:
and 'Variance' = [Actual Revenue] / [Estimated Revenue] - 1 // view this as a percent with 2 fixed digits
This should lay out a flexible control and have QlikSense do the heavy lifting for you. You could even make a drill-down group (Category, Product, RefDate) for an active pivot table, etc.
Note: (We use QlikView, not QlikSense [yet] so my answers reflect a QlikView Document way of doing this analysis)
