topic Re: Setting 100% index in QlikView

Setting 100% index

Mon, 09 Jan 2017 10:30:51 GMT

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

Re: Setting 100% index

rubenmarin — Mon, 09 Jan 2017 11:21:38 GMT

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.

Re: Setting 100% index

Mon, 09 Jan 2017 11:49:32 GMT

Thanks Ruben,

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

Re: Setting 100% index

swuehl — Mon, 09 Jan 2017 15:25:25 GMT

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.