topic Re: Calculate in the same chart two different expression if the value of the dimension change in QlikView

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

caccio88 — Sat, 19 Dec 2015 15:28:35 GMT

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

swuehl — Sat, 19 Dec 2015 16:51:29 GMT

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)

=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

caccio88 — Sat, 19 Dec 2015 18:39:44 GMT

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

swuehl — Sat, 19 Dec 2015 18:49:40 GMT

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

caccio88 — Sat, 19 Dec 2015 19:07:53 GMT

Wonderful!

So grateful! swuehl‌

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

caccio88 — Mon, 21 Dec 2015 08:51:56 GMT

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

swuehl — Mon, 21 Dec 2015 09:23:03 GMT

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, ...)

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

caccio88 — Mon, 21 Dec 2015 09:54:14 GMT

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?