Kindly suggest for Alternate State for a Column in IF Clause

[Anonymous]

Hi All,

I have an Expression which works correctly as below:

if( ProductSubCategory = 'Deo' or ProductSubCategory = 'Perfume' ,

      sum({<TimeRollup={'YTD'}>}  SalesUnits),

     sum({<TimeRollup={'YTD'}>}  SalesVolume)

)

Now, I have an Alternate State and the calculation goes wrong and am not able to put the IF condition around the ProductSubCategory column with Alternate State

I wrote the below condition but it considers Value for ProductSubCategory from the default state and hence calculation goes wrong.

if( ProductSubCategory = 'Deo' or ProductSubCategory = 'Perfume' ,

      sum({StateA<TimeRollup={'YTD'}>}  SalesUnits),

     sum({StateA<TimeRollup={'YTD'}>}  SalesVolume)

)

I am unable to write something like if( {StateA}( ProductSubCategory = 'Deo' or ProductSubCategory = 'Perfume' ). I could not use GetFieldSelections(ProductSubCategory,',','StateA') since it is not necessary User will Select it.

Requesting you to kindly suggest if I could achieve this.

Thanks.

Kuldeep.

[Anil_Babu_Samineni]

For that you need to use Only()

Only({StateA<TimeRollup={'YTD'}>} if( ProductSubCategory = 'Deo' or ProductSubCategory = 'Perfume' ,

      sum({StateA<TimeRollup={'YTD'}>}  SalesUnits),

     sum({StateA<TimeRollup={'YTD'}>}  SalesVolume)

) ProductSubCategory)

[sunny_talwar]

May be this

If(Match(Only({StateA} ProductSubCategory), 'Deo', 'Perfume'),

     Sum({StateA<TimeRollup={'YTD'}>}  SalesUnits),

     Sum({StateA<TimeRollup={'YTD'}>}  SalesVolume)

)

[Anonymous]

Thanks Sunny for the response. I tried to implement the suggestion Expression but I am not getting desired Results.

Attaching here is 12.qvw which is a Dummy Prototype of my Requirement. I am expecting 130 as the Output after putting the Condition.

If User Selects the ProductSubCategory for StateA then the Formula works correctly but not with the Entire Total. Could you please suggest.

Also requesting Other Community members incase they could help.

I tried putting Anil Babu's formula but it gives Error 'Nested aggregations cannot be done'

!

[sunny_talwar · Accepted solution]

You have two options

1) If you are using a straight table, then you can sum of rows for total mode

2) Use Sum(Aggr()) with your chart dimensions

[Anonymous]

Thanks.

I did not have a Straight table but was using this as an Expression in one of my Bar Charts which did not have ProductSubCategory as dimesnion.

However, by using Sum(Aggr( .. I was able to get the desired result

Sum(Aggr(If(Match(Only({StateA} ProductSubCategory), 'Deo', 'Perfume'),Sum({StateA<TimeRollup={'YTD'}>}  SalesUnits),Sum({StateA<TimeRollup={'YTD'}>}  SalesVolume)),ProductSubCategory))

Thanks.

[sunny_talwar]

One thing to change here would be to introduce StateA in your outer aggregation as well

Sum({StateA}Aggr(If(Match(Only({StateA} ProductSubCategory), 'Deo', 'Perfume'),Sum({StateA<TimeRollup={'YTD'}>}  SalesUnits),Sum({StateA<TimeRollup={'YTD'}>}  SalesVolume)),ProductSubCategory))

Or why don't you put the whole chart in alternate state A and then use this

Sum(Aggr(If(Match(ProductSubCategory, 'Deo', 'Perfume'),

Sum({<TimeRollup={'YTD'}>}  SalesUnits),

Sum({<TimeRollup={'YTD'}>}  SalesVolume)), ProductSubCategory))