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

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

Wed, 09 Mar 2011 11:51:04 GMT

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,

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

Wed, 09 Mar 2011 16:14:39 GMT

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

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

Tue, 15 Mar 2011 06:26:42 GMT

No one interested?

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

Tue, 15 Mar 2011 07:53:27 GMT

How are you populating the dimension [Stat]?

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

Tue, 15 Mar 2011 13:00:37 GMT

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

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

Tue, 15 Mar 2011 13:52:49 GMT

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.

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

Tue, 15 Mar 2011 15:06:32 GMT

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

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

Tue, 15 Mar 2011 15:16:01 GMT

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

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

Tue, 15 Mar 2011 16:15:07 GMT

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!

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

Tue, 15 Mar 2011 16:21:41 GMT

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

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

Tue, 15 Mar 2011 17:39:13 GMT

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

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

Tue, 15 Mar 2011 18:13:46 GMT

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

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

Wed, 16 Mar 2011 04:22:41 GMT

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?

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

Wed, 16 Mar 2011 06:53:33 GMT

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.

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

Mon, 21 Mar 2011 15:19:58 GMT

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

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

Mon, 21 Mar 2011 17:33:41 GMT

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?

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

Tue, 22 Mar 2011 02:27:26 GMT

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

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

Tue, 22 Mar 2011 06:02:23 GMT

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!