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]

Any help? I am now confused with what to use as Expression or Dimension

[#3]

No one interested?

[#4]

How are you populating the dimension [Stat]?

[#5]

Hi iassen;

I have attached an example to my initial post. For the dimension [Range], it only contains 4 entries (M, M-1, YTD, YTD-1). I have created it with a distinct from the "AsOf" table, but I also could have used an inline statement.

I purposely named it differently ("DateMonth Range" <> "Range"), so I can use it with other date dimension.

So now, how so I calculate the % of variation between M and M-1? YTD and YTD-1?

And then same for my expression "UnitPrice"

Thanks

[#6]

The easiest thing seems to be to add another two CONCATENATES to your AsOf table. One for %Growth_M where you will load all members for 'M' & 'M-1', but you will have to introduce one more level of grouping so that you can distinguish them in your expression. And the exact same thing for %Growth_YTD, only this time with all memebers for 'YTD' & 'YTD-1'. That should give you the two extra members in your [Range] dimension and you will be able to perform your calculations for them using the original DateMonth Range and the new level of grouping that you will add.

Let me know if you need more details.

[#7]

Thanks iassen, but I don't understand how it is going to help me to perform divisions to calculate %GrowthM/M-1 and %GrowthYTD/YTD-1, whereas for M, M-1, YTD and YTD-1 it simply does aggregation

[#8]

You can use the aggregations and simply divide the aggregated functions.

[#9]

I am still new in QV and can't figure out how to do it with the structure of my data & output table.

Could you give me a sample or the syntax of my expression

Thanks a lot!

[#10]

...it will be something like (SUM(M)-SUM(M1))/SUM(M1).

If you give me a sample app with data, that I can reload, I will be able to show you in details.