Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

kr289720
New Contributor II

Kindly suggest for Alternate State for a Column in IF Clause

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.

1 Solution

Accepted Solutions

Re: Kindly suggest for Alternate State for a Column in IF Clause

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

Capture.PNG

View solution in original post

6 Replies

Re: Kindly suggest for Alternate State for a Column in IF Clause

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Kindly suggest for Alternate State for a Column in IF Clause

May be this

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

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

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

)

kr289720
New Contributor II

Re: Kindly suggest for Alternate State for a Column in IF Clause

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'

!

Re: Kindly suggest for Alternate State for a Column in IF Clause

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

Capture.PNG

View solution in original post

kr289720
New Contributor II

Re: Kindly suggest for Alternate State for a Column in IF Clause

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.

Re: Kindly suggest for Alternate State for a Column in IF Clause

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))