Calculated Dimension in Pivot Table

[#1]

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 ? 

[stigchel]

Simply remove this dimension and add the calculation as an expression

[jfkinspari]

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

[#4]

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 ?

[#5]

Receive error calculated dimension cannot be calculated...

[stigchel]

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],.....)

[#7]

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

[stigchel]

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

Also read this article:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/30/conditional-aggregations

[#9]

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?

[stigchel]

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