topic YTD pivot Help in QlikView

YTD pivot Help

amit_shetty78 — Tue, 08 Sep 2009 20:43:16 GMT

Hi,

I have a table:
Business | Region | Month | Description (Takes values Des1 / Des2)

Month - Jan, Feb, Mar, ..., Dec all for current year.
Description takes values 'Des1' / 'Des2')

Details of what I am trying to do is in the attached xls.

Appreciate any assistance.

Thanks.

YTD pivot Help

amit_shetty78 — Tue, 08 Sep 2009 21:14:50 GMT

Pls ignore previous attachment.

Thanks.

YTD pivot Help

amit_shetty78 — Wed, 09 Sep 2009 15:23:40 GMT

Hi All,

Any suggestions on how to achieve this.

Thanks.

YTD pivot Help

hectorgarcia — Thu, 17 Sep 2009 23:05:42 GMT

have you tried crosstable function?

Hector

YTD pivot Help

tabletuner — Thu, 17 Sep 2009 23:13:53 GMT

This looks like standard pivot table functionality.

By using set analysis you can create the YTD columns of last year.

Can you be more specific in what your problem is?

YTD pivot Help

Thu, 17 Sep 2009 23:49:24 GMT

Here you go, check out the attachment.

In order to get the Totals, you need Partial Sums (Presentation tab). You need partial sums on all your dimensions. In your example, it looks like you had most of them.

The percentage columns require extra expressions. For % Des1 you want:

Sum(Des1) / Sum(TOTAL <Month> Des1)

The others are similar.

YTD pivot Help

amit_shetty78 — Fri, 18 Sep 2009 03:05:24 GMT

Thank you for the reply NMiller and Tjeerd .

But the problem with this is that the YTD is dependent on the month selections. What I want is that irrespective on the months selection the YTD should always be the sum of all months.

As Tjeerd suggests, it may be possible by set analysis but am not sure how to do it.

Can you share some examples to achieve this.

- Amit.

YTD pivot Help

Fri, 18 Sep 2009 04:18:05 GMT

I think it may be possible using the secondarydimensionality() function (I didn't even know it existed until right now).

Secondarydimensionality() seems to equal 0 for the partial sum rows. Therefore, you can set up your expression to respect the selection for the individual months, but ignore the selection for the totals. Something like this:

If(SecondaryDimensionality() = 0, Sum ({1}Des1), Sum(Des1))

The YTD values will be the total of all loaded months, while the chart will only display selected months. See attachment.

YTD pivot Help

amit_shetty78 — Fri, 18 Sep 2009 04:54:36 GMT

Very interesting and super!!

Its amazing to see the use of this function outside the example in help.

Next would be is how to add quarters like YTD into this pivot?

Thanks a lot.