Hi QlikView experts,
I have source data as following
Date Category SubType Quantity Sold Net Sales
1/1/2013 ABC 1A 5 15
1/1/2013 ABC 1B 7 21
2/1/2013 XYZ 1Z 4 24
2/1/2013 LMN M1 2 20
2/1/2013 QQQ 4S 3 18
In QlikView Chart, I need to report, Average Selling Price (ASP) and Selling price Increase (SPI). ASP calculation is simple but SPI calculation involves previous period Quantity Sold and ASP.
ASP = Net Sales / Quantity Sold
SPI = (Current Period ASP – Previous Period ASP) * Current Period Quantity
In QlikView report, Date Category and SubType are the Dimensions.
In the beginning, calculated ASP and SPI during the load at each record so it’s ready for QlikView Chart to display. However, realized that the ASP and SPI numbers are incorrect when chart summarize them at Category, SubType, Month and/or Year levels. Why?
Because the addition (in QlikView function SUM) of dividends (Net Sales divided by Quantity Sold) at different Dimension levels is not the same as Sum of Net Sales divided by Sum of Quantity Sold at same Dimension levels.
Data loaded/calculated in QlikView Script:
Date Category SubType Quantity Sold Net Sales ASP SPI
1/1/2013 ABC 1A 5 55 11 5
1/1/2013 ABC 1B 7 28 4 7
2/1/2013 XYZ 1Z 4 24 6 4
2/1/2013 LMN M1 2 20 10 2
2/1/2013 QQQ 4S 3 6 2 3
(Assuming Previous period ASPs are, 10, 3, 5, 9, 1)
Display in Chart at Category / Month :
Expression: Sum (ASP)
Category ASP
ABC 15
XYZ 6
LMN 10
QQQ 2
Month ASP
Jan-2013 15
Feb-2013 18
Correct/Expected Results:
In QlikView Chart at Category / Month level :
Category ASP
ABC 6.916 (83/12)
XYZ 6
LMN 10
QQQ 2
Month ASP
Jan-2013 6.916 (83/12)
Feb-2013 50.555 (50/9)
If there were only ASP and SPI to calculate it would have been easy to do in the Chart directly.
However there are many other calculations (which also have division and multiplication operations, and some use calculated ASP and SPI figures) are resulting incorrect, if calculations are done in load script and then summarized at dimension level in QlickView Chart.
To complicate it further, there are many calculations that are based on previous and current period numbers and to be presented in Profit and Loss / Balance sheet format (which necessitates to pre-calculate figures and keep in Interval Match type QlikView table, but end up producing wrong results).
What’s the right approach to do this considering results to be presented in Profit/Loss statement format.
Can I build reusable Custom Functions in QlikView Chart e.g. to calculate ASP, SPI which are basis for more complex calculations? How to do it?
I have many different Charts reporting different figures based ASP, SPI so reusable Custom Function in Chart would be handy or the script would get ugly and performance/maintenance nightmare.
I think this is fundamental requirement and it must have been thought and/or have solution for it. I appreciate if you can share your thoughts on how to calculate these figures in backend (load script) have the correct results.
Thanks,
AM
