Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
qw_jakub
Partner - Contributor III
Partner - Contributor III

How to ignore selection in an if statement expression?

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:

IgnoreProblem1.PNG

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.

IgnoreProblem2.PNG

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!

9 Replies
YoussefBelloum
Champion
Champion

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
Partner - Contributor III
Partner - Contributor III
Author

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

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

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

YoussefBelloum
Champion
Champion

Hi tresesco

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

tresesco
MVP
MVP

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

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

qw_jakub
Partner - Contributor III
Partner - Contributor III
Author

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

Try this:

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

sum({<YEAR>} VALUE),

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

, YEAR, KPI)

)

qw_jakub
Partner - Contributor III
Partner - Contributor III
Author

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!