Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

error loading image

How to do that? Thanks in advance,

17 Replies
Not applicable

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

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

Not applicable

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

No one interested?

Not applicable

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

How are you populating the dimension [Stat]?

Not applicable

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

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

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

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

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

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

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

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

Not applicable

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

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

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

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