topic Pivot Table Sub totals in QlikView

Pivot Table Sub totals

lfholland — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Pivot Table Sub totals

Zhandos_Shotan — Mon, 19 Oct 2020 15:21:43 GMT

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'),

)

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)

Re: Pivot Table Sub totals

lfholland — Mon, 19 Oct 2020 17:06:37 GMT

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

Re: Pivot Table Sub totals

Zhandos_Shotan — Mon, 19 Oct 2020 18:40:08 GMT

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

Re: Pivot Table Sub totals

lfholland — Mon, 19 Oct 2020 19:45:21 GMT

Thank you!