How to ignore selection in an if statement expression?

[qw_jakub]

Hello,

suppose i have a table with two dimensions (KPI and YEAR).

I want to set the expression based on the KPI dimension while ignoring the selection in the YEAR dimension. For example:

if(KPI = 'KPI1'

     , SUM({<YEAR = >} VALUE)

          , SUM({<YEAR = >} VALUE)/2

  )

Now when I make a selection in the YEAR dimension, the year is not filtered in the table (that's good), but all the years which were not selected are evaluated as not meeting the condition of the if statement and are calculated with the second ("else") expression (SUM({<YEAR = >} VALUE)/2).

For example, the table with no selections made:

And when I select year 2017: values for KPI1 - 2016 and KPI1 - 2018 are evaluated by the ELSE clause, as if KPI would not equal 'KPI1' for them.

Does anyone have an idea how to truly ignore the selection in and if statement?

Rewriting the expression like this:

sum({<YEAR = >} if(KPI = 'KPI1',VALUE, VALUE/2))

works in this example, but I cannot use it in my real life application (expressions combine more aggregation functions).

Thank You for any ideas!

[YoussefBelloum]

Hi,

I think it is not working as expected for you because on the aggregation you have on your expression, would you be able to share the final expression with all the aggregation ?

actually you need to exclude the year on every aggregation function

[qw_jakub]

Hello,

In my real life application I have a set of KPIs which are mostly calculated in a standard way (SUM(VALUE)) but I have some KPIs which need to be calculated in a different more complicate way.

It looks something like this:

if(KPI = 'KPI1'

     , (sum(TOTAL <YEAR> {<YEAR = , KPI = {'KPI3'}>} VALUE)

          / sum(TOTAL <YEAR>  {<YEAR = , KPI = {'KPI2'}>} VALUE)) ,

     , SUM({<YEAR = >} VALUE)/2)

This works fine when no YEAR is selected, but when I select a year, for KPI1 it is calculated right only for the selected year. The other years are calculated by the else clause.

I have also updated the example application.

Thank You!

[tresesco]

Try putting your 'If' withing only(), like:

Only( {<Year>} If(...<your expression>) )

[YoussefBelloum]

Hi tresesco

he will need to use AGGR() on the KPI and YEAR dimensions before ONLY() right ?

[tresesco]

Probably. I din't even go through the entire discussion and quickly jumped into proposing a solution. Let me have re-look at the requirement in bit more detail.

[YoussefBelloum]

Yes, because it is a bit weird here, I have doubts about the if and the values set on the set analysis

[qw_jakub]

I have tried this:

Only( {<YEAR=>} aggr(

          if(KPI = 'KPI1',

               sum({<YEAR=>} VALUE),

                    SUM({<YEAR=>}VALUE)/2)

     , YEAR, KPI))

But the behavior is the same. When I select a Year, for the KPI1 the other years get calculated by the IF clause.

Thank You!

[tresesco]

Try this:

Only({<YEAR>} Aggr( if(only({<YEAR>}KPI) = 'KPI1',

sum({<YEAR>} VALUE),

SUM({<YEAR>} VALUE)/2)

, YEAR, KPI)

)

[qw_jakub]

This works, how I wanted!

After a little tuning, it turns out that just adding the set analysis (in an only function) to the IF statements does the job:

if(Only({<YEAR = >} KPI) = 'KPI1',

sum({<YEAR = >} VALUE),

SUM({<YEAR = >} VALUE)/2)

Thank You both for the help!