topic Re: Formula does not work in QlikView

Formula does not work

danialier — Tue, 04 Mar 2014 12:16:11 GMT

Hello,

Can anyone help me on that ? I have a Bar Chart where I want to show the evolution of the hardware units but in each column (so in each month) I want to show the delta of units from the current month vs the previous one.

As an example, if I have: January 22units sold / Februrary 32 units sold / March 15 units sold. Then I want the columns to show a bar as follows: February a total of +10units (this is 32-22 units) and March a total of -17units (this is 15-32 units).

I'm using the following formula but it does not work...

=num(sum({<Product={'Hardware'},Month={$(=Month)}>}Units) - sum({<Product={'Hardware'},Month={$(=(Month)-1)}>}Units))

Thanks!

dani

Re: Formula does not work

nilesh_gangurde — Tue, 04 Mar 2014 12:23:59 GMT

Hi,

in set analysis it will not work.

you can calculate one more field at script level which will show the value after subtraction.

-Nilesh

Re: Formula does not work

Tue, 04 Mar 2014 12:28:27 GMT

You are not able to use set analysis in this case.

Either

1. Handle in load script level.

2. Create another expression and make it invisible.

Invisible Express (Helper): sum({<Product={'Hardware'}>}Units)

Actual Express for bar chart: =Helper - Above(Helper)

Re: Formula does not work

Tue, 04 Mar 2014 13:15:59 GMT

Dani,

Set Analysis are computed ONCE per chart. They cannot return different values according to the row or column of the chart. So, it will certainly not work.

If you want different result per line/row:

You will need to use functions like above() or before()

You will need to modify your model. See a doc I have written on that topic: http://community.qlik.com/docs/DOC-4821

Fabrice

Re: Formula does not work

veidlburkhard — Tue, 04 Mar 2014 13:45:56 GMT

Hi Dani, why don't you calculate the shift in the script and use the simple expression (Units Sold - Shift) in your bar chart.

See example below:

Re: Formula does not work

PrashantSangle — Tue, 04 Mar 2014 14:31:32 GMT

Hi,

try this

=num(sum({<Product={'Hardware'},Month={'$(=Month)'}>}Units)) - num(sum({<Product={'Hardware'},Month={$(=Month(MonthStart(Max(Date))-1))}>}Units))

Re: Formula does not work

jonathandienst — Tue, 04 Mar 2014 14:44:07 GMT

As others have said, you cannot use set analysis in this context, but Above() may be what you need if you want to do the calculation in an expression and not in script. Something like this

=sum({<Product={'Hardware'}>}Units) - Above(sum({<Product={'Hardware'}>}Units))

(Above(TOTAL ...) if you have multiple dimensions)

HTH

Jonathan

Re: Formula does not work

danialier — Tue, 04 Mar 2014 14:58:58 GMT

many thanks all !!! issue resolved