Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Creator

## Pivot Table Sub totals

I have a pivot table I have created.  The requirement is to have Net Sales/Margin down the side and broken up by months across the top with YTD last year, YTD this year and Variance at the end.  No problems with that.  This issue is getting a subtotal.  It will only display the subtotal for the Net Margin % and not both Net Sales and Margin %.

My dimension for Net Sales/Margin % is the following:

=ValueList('Net Sales','Net Margin %')

I have three other dimensions along the left-hand side that are just the dimensions selected, not calculated dimensions.

My expression for one of the months is the following:

If(ValueList('Net Sales','Net Margin %')='Net Sales',

/
)

Below is a screen shot of what it is doing.   Should I change the design to get both subtotals, if so how?  Or is there another solution?  Any help is appreciated.  Thank you!

1 Solution

Accepted Solutions
Partner - Creator II

Hi!

Try this:

1) Add to PlanGroup dimension real 'Total' text value.

2) Use additional condition for measures. First condition is for 'Total' dimension value with TOTAL<PlanGroup > addition, and orignal measure expression for another values:

IF(PlanGroup='Total',

If(ValueList('Net Sales','Net Margin %')='Net Sales',

/
)

,  //ELSE

If(ValueList('Net Sales','Net Margin %')='Net Sales',

/
)

)

Also for better performance and readabilty, you can replace valuelist with real island table dimension

[Measures

'Net Sales'

'Net Margin %'];

and replace If(ValueList('Net Sales','Net Margin %')='Net Sales' , expr1, expr2)

with

Pick(Match(Measures, 'Net Sales', 'Net Margin %'), expr1, expr2)

4 Replies
Partner - Creator II

Hi!

Try this:

1) Add to PlanGroup dimension real 'Total' text value.

2) Use additional condition for measures. First condition is for 'Total' dimension value with TOTAL<PlanGroup > addition, and orignal measure expression for another values:

IF(PlanGroup='Total',

If(ValueList('Net Sales','Net Margin %')='Net Sales',

/
)

,  //ELSE

If(ValueList('Net Sales','Net Margin %')='Net Sales',

/
)

)

Also for better performance and readabilty, you can replace valuelist with real island table dimension

[Measures

'Net Sales'

'Net Margin %'];

and replace If(ValueList('Net Sales','Net Margin %')='Net Sales' , expr1, expr2)

with

Pick(Match(Measures, 'Net Sales', 'Net Margin %'), expr1, expr2)

Creator
Author

How do I "1) Add to PlanGroup dimension real 'Total' text value." ? Once I figure that out, I think this solution will work! 🙂

Partner - Creator II

Adding dummy row to table with PlanGroup dimension at script. Something like:

'Total' as PlanGroup,

0 as Fieldname1

0 as Fieldname2

...

its depends on your data model.

OR,

you can preagregate values and join Total row to table:  ..Sum(NetSales) ... group by PlanGroup..

But exclude Total row with measures when its unnesesary: <PlanGroup -={'Total'}>

Creator
Author

Thank you!

Community Browser