topic Re: Re: Customize Sub-Totals in a Pivot Chart in QlikView

Customize Sub-Totals in a Pivot Chart

Mon, 15 Sep 2014 14:58:50 GMT

I have a pivot chart that has several dimension (year/month/region/state) and two measures (Sales/Units). The measures are set to a toggle where when SALES is selected from the toggle filter - only the sales figures show - pivoted by month. Similarly, when Units is selected in a toggle filter, only the UNITS display - pivoted by month.

The challenge is to define the sub-total calculation. When Sales is displayed - the subtotal formula should be set to SUM. When UNITS are displayed, the sub-total formula should be set to AVERAGE.

Any help would be appreciated.

Thanks.

Tom

Re: Customize Sub-Totals in a Pivot Chart

JonnyPoole — Mon, 15 Sep 2014 15:09:28 GMT

Hi Tom !

Are you using a data model field or a variable to store the 'toggle' ?

I think you just need an expression like this:

if ( <..toggle...> = 'Sales' , sum(Sales), if ( <..toggle...> = 'Units' ,sum(Units) ) )

but if you are doing other things in the expression that require a more fine tuned example can you post the current expression or the qvw ?

cheers,

Jonny Poole

Re: Customize Sub-Totals in a Pivot Chart

Mon, 15 Sep 2014 15:15:21 GMT

I am using a Set Analysis statement to sum the Sales figures…..otherwise your logic is similar to what I have

Thomas Berard | PMO Consultant | Global Technology, RAD-Americas Planning & Effectiveness |<https://team.amer.mymetlife.com/Teams/bmosecure/default.aspx>

Tel: 401-827-2867 | Cell: 401-556-0239 | Email: tberard@metlife.com<mailto:tberard@metlife.com>

To submit Service Requests click here for Assist<https://team.amer.mymetlife.com/Teams/IBITPMO/Web%20Part%20Page%20Library/SN%20in%20Connect.aspx?PageView=Shared&InitialTabId=Ribbon.WebPartPage&VisibilityContext=WSSWebPartPage>

Re: Customize Sub-Totals in a Pivot Chart

JonnyPoole — Mon, 15 Sep 2014 15:18:49 GMT

ok the approach SHOULD still work. IE: if the toggle has sales selected then the entire expression below will be used including any SET ANALYSIS statements in the sum( ) expression for sales .

if ( <..toggle...> = 'Sales' , sum( {SETSTATEMENT} Sales), if ( <..toggle...> = 'Units' ,sum(Units) ) )

do you see an issue ?

Re: Customize Sub-Totals in a Pivot Chart

Mon, 15 Sep 2014 15:22:33 GMT

That is the logic I have and it works. My issue is that the user wants to see a sub-total calculation but that the formula varies dependent on the toggle. When it is set to Sales – it should be a SUM; when it is set to Units – it should calculate the AVG.

Thomas Berard | PMO Consultant | Global Technology, RAD-Americas Planning & Effectiveness |<https://team.amer.mymetlife.com/Teams/bmosecure/default.aspx>

Tel: 401-827-2867 | Cell: 401-556-0239 | Email: tberard@metlife.com<mailto:tberard@metlife.com>

Re: Customize Sub-Totals in a Pivot Chart

jvitantonio — Mon, 15 Sep 2014 15:24:16 GMT

Hi,

Take a look at the attached app.

Re: Customize Sub-Totals in a Pivot Chart

JonnyPoole — Mon, 15 Sep 2014 15:26:52 GMT

got it... missed the need for average in the post. my oversight. good solution below.

Re: Customize Sub-Totals in a Pivot Chart

Mon, 15 Sep 2014 17:14:10 GMT

Closer….

The total number for the UNITS for the month should be the sum or all the units for the Month. However, the Sub-Total in the pivot should be the AVG.

The solution you had assumed that there was an average of all of the detail records. I added a line to your inline data:

This is my result.

I would want to see a Subtotal AVG of 13.

Thanks.

Thomas Berard | PMO Consultant | Global Technology, RAD-Americas Planning & Effectiveness |<https://team.amer.mymetlife.com/Teams/bmosecure/default.aspx>

Tel: 401-827-2867 | Cell: 401-556-0239 | Email: tberard@metlife.com<mailto:tberard@metlife.com>

Re: Re: Customize Sub-Totals in a Pivot Chart

tresB — Mon, 15 Sep 2014 17:23:29 GMT

Use SecondaryDimensionality() like in attached qvw.

Re: Re: Customize Sub-Totals in a Pivot Chart

Mon, 15 Sep 2014 20:03:08 GMT

i dont see this working as planned

Re: Re: Re: Customize Sub-Totals in a Pivot Chart

jvitantonio — Tue, 16 Sep 2014 08:40:25 GMT

Hi,

Here's the app updated.

Re: Customize Sub-Totals in a Pivot Chart

Tue, 16 Sep 2014 10:54:45 GMT

Close!

This is the display I am looking for. The Sub-Total across Rows (2014-15.67) is perfect. I am looking for the column sub-totals to be the Total (month 1 – should be 37).

Thanks. Marking the response as correct.

Thomas Berard | PMO Consultant | Global Technology, RAD-Americas Planning & Effectiveness |<https://team.amer.mymetlife.com/Teams/bmosecure/default.aspx>

Tel: 401-827-2867 | Cell: 401-556-0239 | Email: tberard@metlife.com<mailto:tberard@metlife.com>