Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Setting 100% index

Hi,

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

pandl.jpg

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

1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

3 Replies
rubenmarin

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.

Not applicable
Author

Thanks Ruben,

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

swuehl
MVP
MVP

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.