topic Re: Calculated Dimension in Pivot Table in QlikView

Calculated Dimension in Pivot Table

Wed, 02 Jul 2014 08:58:41 GMT

I have a Pivot Table with calculated dimension Year of First Revenue.

The expression for the calculated dimension is

=IF($(=Sum({<[Fiscal Year]={$(=(vCurrentYear)-2)}>}[Sales Amount])) = 0, 'None', $(=(vCurrentYear)-2))

For now, the calculation of the calculated dimension is exactly the result of this expression, that is to say it is a constant in all cells of the Pivot Table. I would like that the expression is evaluated for each Product Group / Sub Group / Type / Country / Zone Manager / Category. Then, the result should be 'None' for the second cell for instance.

Is there a possibility to have the calculated dimension calculated by Product Group / Sub Group / Type / Country / Zone Manager / Category ?

Re: Calculated Dimension in Pivot Table

stigchel — Wed, 02 Jul 2014 09:31:04 GMT

Simply remove this dimension and add the calculation as an expression

Re: Calculated Dimension in Pivot Table

jfkinspari — Wed, 02 Jul 2014 09:34:24 GMT

Or perhaps remove the $-expansions, that are outside of the Set Analysis

Re: Calculated Dimension in Pivot Table

Wed, 02 Jul 2014 10:05:04 GMT

if I change for an expression, the expression is calculated for each Fiscal Year separately. I slightly changed the expression to

=IF($(=Sum({<[Fiscal Year]={$(=(vCurrentYear)-2)}>}[Sales Amount])) = 0, 'None', [Fiscal Year])

and obtain

What i want is a single date for each cell by Product Group / Sub Group / Type / Country / Zone Manager / Category indicating the first year Revenue is not Null.

I tried to change for this syntax, but not correct...

=IF($(=Sum([Sales Amount])) = 0 AND $(=Sum({<[Fiscal Year]=$(=[Fiscal Year]+1))}>}[Sales Amount])) = 0, 'None', [Fiscal Year])

Would you have an hint how to proceed ?

Re: Calculated Dimension in Pivot Table

Wed, 02 Jul 2014 10:05:28 GMT

Receive error calculated dimension cannot be calculated...

Re: Calculated Dimension in Pivot Table

stigchel — Wed, 02 Jul 2014 10:20:12 GMT

I missed the Fiscal Year dimension in your first picture. You could move back to your first scenario (Calculated dimension) but add an Aggr function over the dimensions you want included.

Aggr(YourExpression, [Product Group], [Sub Group],.....)

Re: Calculated Dimension in Pivot Table

Wed, 02 Jul 2014 11:43:11 GMT

Thanks for reply!

Using this expression with the AGGR

=IF(IsNull(AGGR($(=Sum({<[Fiscal Year]={$(=(vCurrentYear)-2)}>}[Sales Amount])),[Product Group Desc], [Product Sub Group Desc], [Product Type Desc],[Country],[ZoneMgrName])), 'None', [Fiscal Year])

it is still not agreggating on the fields i am listing

Re: Calculated Dimension in Pivot Table

stigchel — Wed, 02 Jul 2014 11:50:39 GMT

No, the if should be within the aggregation...

Re: Calculated Dimension in Pivot Table

Wed, 02 Jul 2014 12:21:59 GMT

Thanks!

I changed for this expression

=AGGR(IF(IsNull($(=Sum({<[Fiscal Year]={$(=(vCurrentYear)-2)}>}[Sales Amount]))), AGGR(IF(IsNull($(=Sum({<[Fiscal Year]={$(=(vCurrentYear)-1)}>}[Sales Amount]))), AGGR(IF(IsNull($(=Sum({<[Fiscal Year]={$(=(vCurrentYear))}>}[Sales Amount]))),'None', $(=(vCurrentYear)))), $(=(vCurrentYear)-1))), $(=(vCurrentYear)-2)),[Product Group Desc], [Product Sub Group Desc], [Product Type Desc],[Country],[ZoneMgrName])

For clarity, the expression looks like this, where instead of nested conditions if have the same logic for testing if 2010 or 2011 is the first year.

=AGGR(IF(IsNull($(=Sum({<[Fiscal Year]={$(=(vCurrentYear)-2)}>}[Sales Amount]))),

' nested conditions,

$(=(vCurrentYear)-2)),

[Product Group Desc], [Product Sub Group Desc], [Product Type Desc],[Country],[ZoneMgrName])

Result is the following:

so that None ( - ) is the result forall fields. What is wrong with the syntax I am using above?

Re: Calculated Dimension in Pivot Table

stigchel — Wed, 02 Jul 2014 13:00:34 GMT

I'm not sure if I'm getting your logic here, but let me clarify some things:

- $ sign expansions are evaluated once for the whole chart, not for each row or value in the dimensions (If you want to know what it is, it is displayed in the header of the expression when you leave the label blank (as above))

- An Aggr(Expr,Dimensions) always needs it dimensions, either include them for all Aggr( functions you use (Why?) or remove all other Aggr() but the first.

Have you read the article, in general it is better to flag the data in load script. An other idea would be to do a Clever calculation like (Sum(Amount)*Year)/Sum(Amount), which will be the year for where you have amounts, null where you don't

Further then this I don't think I will be able to help you