Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

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

Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

Thank you!