YTD pivot Help

[amit_shetty78]

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.

[amit_shetty78]

Pls ignore previous attachment.

Thanks.

[amit_shetty78]

Hi All,

Any suggestions on how to achieve this.

Thanks.

[hectorgarcia]

have you tried crosstable function?

Hector

[tabletuner]

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?

[#6]

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.

[amit_shetty78]

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.

[#8 · Accepted solution]

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.

[amit_shetty78]

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.