Calculate in the same chart two different expression if the value of the dimension change

[caccio88]

Hi everyone,

I woul like to use in the same chart for the first 4 months an expression and for the lat 3 months another one.

Is it possible?

Is my issue clear enough ?

Her attached my qvw.

Thank u all.

Filiberto

[swuehl]

You just need to add a conditional statement to check your dimension value / line number, e.g.

=If(Rowno()<=4,(sum(CMV_M)*30)/1000,(sum(CMV_W)*7)/1000)

or

=If(Data <= '01/06/2015',(sum(CMV_M)*30)/1000,(sum(CMV_W)*7)/1000)

[caccio88]

Thank u swuehl‌! it's a great contribute for me!

one question: what if I should add data for a new month (like 01/10/2015)? it seems that the expression is not working anymore, isn't it?

I try to be a bit clearer, what i would like to have is that the last 3 months has been calculeted every time with sum(CMV_W)*7)/1000 and the months before with sum(CMV_M)*30)/1000.

[swuehl]

There are a couple of ways to check your dimension values, e.g.

If(Rowno()<=NoOfRows()-3,(sum(CMV_M)*30)/1000,(sum(CMV_W)*7)/1000)

[caccio88]

Wonderful!

So grateful! swuehl‌

[caccio88]

Hi swuehl‌, i'm thinking if it's possible to define the "if statement" based on the DateMonth and not on the row number.

something like: if(Data<(max(Data)-3,(sum(CMV_M)*30)/1000,(sum(CMV_W)*7)/1000));

where "Data" is like the Month date.

This is because if I add another dimension like "Codice Articolo" the formula will not work anymore, isn't it?

[swuehl · Accepted solution]

Should be possible, but you need something like

If(Data <= AddMonths(Max(TOTAL Data),-3), ....)

Or if you are using a sequential Month number field, like

If(MonthNo <= Max(TOTAL MonthNo)-3, ...)

[caccio88]

Thank u so much swuehl‌!! that's so much better.

I need another time your help... I've introduced an if statement in my qvw here attached and another dimension "Codice Articolo" cause i want to know the sum of value of "Stock/1000" per month stacked per "KO" case and "OK" case.

Is my issue clear enough?