7 Replies Latest reply: Dec 21, 2015 4:54 AM by Filiberto Cacciari

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

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

• ###### Re: Calculate in the same chart two different expression if the value of the dimension change

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)

• ###### Re: Calculate in the same chart two different expression if the value of the dimension change

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.

• ###### Re: Calculate in the same chart two different expression if the value of the dimension change

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)

• ###### Re: Calculate in the same chart two different expression if the value of the dimension change

Wonderful!

So grateful! swuehl

• ###### Re: Calculate in the same chart two different expression if the value of the dimension change

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?

• ###### Re: Calculate in the same chart two different expression if the value of the dimension change

Should be possible, but you need something like

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

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

• ###### Re: Calculate in the same chart two different expression if the value of the dimension change

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?