Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lfholland
Creator
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',

Num(sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetSales ),'$###,###,##0'),

Num((sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetMargin )
/
sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetSales )),'###.#0%')
)

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!

lfholland_0-1603115632000.png

 

1 Solution

Accepted Solutions
Zhandos_Shotan
Partner - Creator II
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',

Num(sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>} TOTAL<PlanGroup > NetSales ),'$###,###,##0'),

Num((sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}TOTAL<PlanGroup > NetMargin )
/
sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>} TOTAL<PlanGroup > NetSales )),'###.#0%')
)

,  //ELSE

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

Num(sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetSales ),'$###,###,##0'),

Num((sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetMargin )
/
sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetSales )),'###.#0%')
)

)

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

Load * inline

[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)

 

View solution in original post

4 Replies
Zhandos_Shotan
Partner - Creator II
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',

Num(sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>} TOTAL<PlanGroup > NetSales ),'$###,###,##0'),

Num((sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}TOTAL<PlanGroup > NetMargin )
/
sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>} TOTAL<PlanGroup > NetSales )),'###.#0%')
)

,  //ELSE

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

Num(sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetSales ),'$###,###,##0'),

Num((sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetMargin )
/
sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetSales )),'###.#0%')
)

)

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

Load * inline

[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)

 

lfholland
Creator
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! 🙂

Zhandos_Shotan
Partner - Creator II
Partner - Creator II

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

Concatenate load

'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'}>

lfholland
Creator
Creator
Author

Thank you!