Growth of unit price / percentage of evolution. It looks simple but....

[#1]

Hello,

It looks easy on the paper, but how do you do produce such table?

I have a dimension [Stat] which contains the value M, M-1, YTD, YTD-1

I am creating a AsOf table to easily calculate those statistics (http://community.qlik.com/forums/t/41759.aspx)

Sales & Qty are expression such as:

Sales: Sum({${$<[As of Month]=SliderMonth>}Sales)

Qty: Sum({${$<[As of Month]=SliderMonth>}Qty)

UnitPrice: Sales / Qty

1st question: How do I add 2 extra member (%Growth_M and %Growth_YTD) to my dimension [Stat]?

Calculation for Growth members are (M - (M-1))/(M-1) and (YTD - (YTD-1))/(YTD - 1)

Am i going to adjust the expression Sales & Qty to answer to that?

2nd (remark): I need to make sure that the growth of Unit Price is calculated correctly;

- it should be (UnitPrice(M) - UnitPrice(M-1))/UnitPrice(M-1)

- and not %Growth_M(Sales) / %Growth_M(Qty) ==> totally wrong

How to do that? Thanks in advance,

[#2]

Thank you for your time. I now attached the fact data (*.xlsx) that I use to build the QV example above.

Looking forward your response

[#3]

Here's the general idea... I have done only the first one, but the second one will go the same way. Enjoy! 🙂

[#4]

Hi iassen,

Thanks for your time. I have looked at your solution, I was expecting something more generic, like using another expression or calculated dimension for %growth_M, which will be applied to my existing expressions Qty & Sales.

Modifying each of my expression to add if statements to test which stat I want to calculate is totally inconceivable for my users

I am expected an expression which says %growth_M = (M - (M-1))/(M-1). and it will get apply to my Qty and Sales automatically

On your example, you have alterate the "AsOf" table to add a field "DateMonth Sub Range", but you do not make any use of it. If it was for adding the element "%growth_M", we could just add it in the independant column "Range". No?

Thanks. Any other experience / suggestion?

[#5]

With your current set up, building a simple formula is not possible. If you want to use M, M-1, etc. you will have to have separate expressions for them and not use this Range column. Check it now?

PS: Yes, that sub range column is not used. I left it there for debugging purposes.

[#6]

Hi Iassen

Sorry for the late reply. I have played in the meantime with Excel 2010 and their pivot table; it is so easy (although not everything is correct) to add new field and new item in an existing dimension. I do not know if you can open the attached Excel file to see.

There is still a solve order problem between field & item. All cells highlighted in red are wrong as it does not calculate the growth of unit price but the 'unit price of the growth'.

I am amazed about this limitation in QV where the only solution is to code all the if conditions...

[#7]

I wouldn't call it a limitation, but rather more complex. On the other hand, we can do things in QlikView that are not possible in XLS, so it's a question of what do you want to do and picking the best tool to do so. Anyhow, I didn't get you...is my solution giving you wrong results?

[#8]

Hi Iassen,

Thanks again for your time, your answers are working, but if I multiply the number of columns (statistics) and rows (expression), this won't be handy for my end-user. I was hoping for a simpler solution such as what Excel does with its PivotTable.

Thanks

[#9]

Well, I have pretty much given you the two options that I know of: (1) Named columns in the chart, and (2) named columns in the script.

Keep in mind that the general intent in QlikView is to have reports pre-made for your end users, so they don't spend time creating them. Although, there is this new plug-in for Excel where you can connect an xls file to a qvw file being the datasource and you can use the flexible formulas in xls. You can check it here: http://www.qvexcel.com/

Let me know if you need further assistance.

Cheers!