topic Set analysis in Pivot table sub total / Customized total in QlikView

Set analysis in Pivot table sub total / Customized total

reshma_km — Thu, 23 Jun 2016 11:03:10 GMT

Hi,

I have a pivot table with “month year” as dimension and 5 expressions in the below format. We show rolling 12 months data based on user selections.

	month 1	month 2	…	month 12
expression 1	x	x	x	x
…	x	x	x	x
expression 5	x	x	x	x

I want to add Fiscal year total as the last column to this. Ie, Fiscal year totals should be populated in the last column irrespective of the months being displayed.

	month 1	month 2	…	month 12	FY Totals
expression 1	x	x	x	x	x
…	x	x	x	x	x
expression 5	x	x	x	x	x

Also, each total row has the expression mentioned in that row.

Can you please help me how to achieve this?

Re: Set analysis in Pivot table sub total / Customized total

marcus_sommer — Thu, 23 Jun 2016 13:43:05 GMT

One way could be to add the value of "FY Totals" to your "month year" field (maybe you create an extra one only for this purpose) and then you could query these value within the expressions, like:

if([month year] = 'FY Totals', ExpressionFYTotal, Expression1)

Another way would be to add simply a further dimension as a calculated dimension like: = 'FY Totals' and enabling partial sums for this dimension and within the expressions you query the dimensionality, like:

if(secondarydimensionality() = 0, ExpressionFYTotal, Expression1)

- Marcus