Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Not applicable

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

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,

17 Replies
Not applicable
Author

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

Not applicable
Author

No one interested?

Not applicable
Author

How are you populating the dimension [Stat]?

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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!

Not applicable
Author

...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.

Community Browser