If statement within set analysis

[zagzebski]

I am trying to find the most efficient way to address two flags that we use... (1) a flag that is for Year to Date amount and (2) a flag that is for the last 12 months.

I want to incorporate the best way to address the flags (above) when a user clicks the "YTD" button or the "TMT" (twelve months trailing) button.

So what is the most efficient way to choose one of these expressions based on the YTD or TMT selection. Is there way to have an If statment WITHIN the set analysis of one expression?

I have about 75 charts I have to address this with so looking for the most efficient way!  The stripped down version of the expressions I need based on user selection:

sum({<YTD={'1'}>}Amount)

and

sum({<TMT={'1'}>}Amount)

THanks in advance

[tresesco]

It should be IF outside set analysis and a simple one like:

=If( <TMT condition>, sum({<TMT={'1'}>}Amount) , sum({<YTD={'1'}>}Amount) )

Or, you might want to put another condition in else part like:

If( <TMT condition>, sum({<TMT={'1'}>}Amount) , If( <YTD Condition>, sum({<YTD={'1'}>}Amount) ))

[Anonymous]

How about :

• Creating a couple of variables
• Having your buttons set the variable values
  • As in YTD button sets it to sum({<YTD={'1'}>}Amount
• Use a dollar expansion of the variable in your expression

[Anonymous]

if the flags are always contrary (means YTD = 1 while TMT = 0 and vice versa) you do not need an if statement.

are the flags built in your calendar and connected to the facttable?

why

     sum(Amount)

is not enough? pressing the button should select YTD or TMT and the correct data should be selected. I would build my datamodel to do so

[zagzebski]

Well my expressions have a little more in that each chart accounts for 2 previous time frames so:

Expression 1 : YTD this year or Last 12 months (depending on YTD or TMT selection)

Expression 2: YTD last year or prior 13-24 months (depending on YTD or TMT selection)

Expression 3: YTD 2 years prior or prior 25-36 months (depending on YTD or TMT selection)

So I have additional criteria in the set that includes the max, max-1, max-2 for year in the expressions, but I do not want that part of the set when TMT is selected as it will cross multiple years (ie August 2013-July 2014 when TMT is selected)

Make sense?

[zagzebski]

I really like where this is headed but it is new to me so a question:

Would the expression have just one variable in it? I know in theory what you are saying but struggling to figure out what the expression would look like and how it would change depending on button selection.

[Anonymous]

I have a similiar application where I show the YTD or TMT data of the year and the previous year

my expression is

sum ({>...

         Kalenderdates={'>=$(=vDateFrom)<=$(=vDateTo)'},

          ....>}

        )FactValue

The variables vDateFrom and vDateTo are set (depending on YTD or TMT)

e.g. 1.1.2014 to 30.06.2014 YTD or 1.3.2014 to 30.4.2014 TMT

A similiar expression and variables can do it for Prev Year and Year -2

in my application the user switches between YTD and  TMT using a button

Result: my expressions in many charts are much easier to read and maintain

[zagzebski]

I like it. Just kind of new to me so thinking about what the specifics would be. So you click on YTD and it populates the variable with one expression, then if they click on the TMT button it populates the variable with a different expression? Then the variable(s) is utilized in the expression?

[Anonymous]

yes, that's it. my variable

vDateFrom = if YTD,yearStart(SelDate),monthstart(SelDate)  (shows principal)

vDateTo = monthend(SelDate)

if minimum date to report is month,

User selects July!

YTD: vDateFrom: 1.1.2014; vDateTo 31.7.2014

TMT vDateFrom: 1.7.2014; vDateTo: 31.7.2014

if you select more than 1 month (only for TMT) then the variables correspond to the months

hope that gives you an idea

[zagzebski]

And the additional twist (sorry) is that if neither YTD or TMT buttons are selected the expression should just respected whatever calendar dates are selected.