topic Re: how can i create this pivot? in QlikView

how can i create this pivot?

amien — Thu, 12 May 2011 11:28:22 GMT

table looks like this:

id, date, profit, costs, discount

pivot should look like this:

year = 2010 | year = 2011 | grow %

-------------------------------------------------------------------

profit | expression | expressions | expression

costs | | |

discount | | |

i can put year in dimensions, add serval expressions (profit, costs, discount) and than drag the expressions to the dimension location and the dimension location to the top. works great, but than i cant add the % column .. how can this be designed?

Re: how can i create this pivot?

stephencredmond — Thu, 12 May 2011 11:34:47 GMT

There are several ways of doing this. Depending on your requirements, you might want to look at a set. However, the easiest way is that you keep the chart that you have with Year pivoted across the top and a 2nd expression for the growth using the Before function. E.g.:

(Sum(LineSalesAmount)-Before(Sum(LineSalesAmount)))/Sum(LineSalesAmount)

In the first column, this value will be null (because there is no "Before") but it will calculate for other years. This allows you to have several years in your pivot and display the growth for all of them.

Regards,

Stephen

Re: how can i create this pivot?

stephencredmond — Thu, 12 May 2011 11:38:52 GMT

Ah!

Just re-read your original and see that you already have several expressions and have pivoted them.

Unfortunately you can't pivot individual expressions - you have to do them all. But you can still use my expression, just one for each calculation and then you have the growth figures under each of the other expressions rather than to the side.

Regards,

Stephen

how can i create this pivot?

amien — Thu, 12 May 2011 14:27:00 GMT

i solved this with reading the source data with a CROSSTABLE function.