Setting 100% index

[#1]

Hi,

I've been asked to create the following pivot table:

My problem is with 100% of the net sales line.

If I use 'show relative', it shows relative against the grand total (Margin).

If I use set analyse, like {<account =  {net sales}>}, the % is only shown on the net sales line

If I use a variable to store 'sum({<account = {net sales}>} amount), it calculates against the total of all periods, not per month.

Dimension months is a cyclic group, can also be year or week

It's a pivot table

Someone has an idea, what formula to use for the expression of column 2 and 4: the %

Thanks,

Johan

[rubenmarin · Accepted solution]

Hi Johan, I think you need to add TOTAL to your expression, like:

Sum(TOTAL <Month> {<account = {net sales}>} amount)

Having a group in the dimension needs to add a $-expansion to get the current field selected in group:

Sum(TOTAL <[$(=GetCurrentField(GroupName))]> {<account = {net sales}>} amount)

Hope this helps.

[#3]

Thanks Ruben,

Exactly what I needed. Especially the cyclic group gave me a headache.

[swuehl]

Johan, if your issue is resolved, then please close this thread by marking Ruben's answer as correct.

Qlik Community Tip: Marking Replies as Correct or Helpful

As an alternative to the dollar sign expansion, you can also add all the group's field names to the TOTAL qualifier field list. Might be easier to read & write if your group is quite small.