topic Re: Last selected value minus first selected value in QlikView

Last selected value minus first selected value

Thu, 17 Sep 2015 06:45:12 GMT

Hello,

I have the following expression:

Aggr (Sum (TL1),[day/month/year],P1)

Where TL1 is current quantity of liquid (which input on daily basic) and P1 is the type of the liquid.

I want to show, for every selected period ,which the user can make for this chart, what is the difference between the last selected value from the first value

Surly if I knew how to calculate the last and first value it will do.

For example if I select from 23 to April to 17 of may , and every day hold a value I which to know what is the difference between the value of 17.5 minus 23.4 (and see how much liquid went loss).

How does is combine with the fact that I have more than one liquid ? since the user can show in the chart 4 diff. liquids or filter only one.

Thank you for your help!!!!

Nir GL

Re: Last selected value minus first selected value

Digvijay_Singh — Thu, 17 Sep 2015 06:56:03 GMT

Can you post sample data and desired output, see if below details can help to calculate top value, taken from ref manual -

Re: Last selected value minus first selected value

Thu, 17 Sep 2015 07:05:21 GMT

Unfortunately I am not allowed to export the data BUT

let say I have the following data for the last week:

10.9 = 100 (liters)

11.9=99

12.9=97

13.9=97

14.9=96.5

15.9=95

16.9=94

17.9=90

Now the user can select the whole week (10-17.9) than I wish to see in a Text Box (or what ever) the calculated value 10 (100-90).

The user can also select 13-16.9 than I wish to see the calculated value 3 (97-94).

This is for Tank 1 which is in Group A in country X.

I have many tanks of liquid in Group A and more than one group in Country X.

In addition i Have different liquids in the different tank (not mix in one tank of course)

Nir

Re: Last selected value minus first selected value

Digvijay_Singh — Thu, 17 Sep 2015 08:06:10 GMT

Try like attached -

=Sum({<Week={$(=min(Week))}>}Aggr(Sum(Value),Week))-Sum({<Week={$(=max(Week))}>}Aggr(Sum(Value),Week))

Re: Last selected value minus first selected value

Thu, 17 Sep 2015 08:38:15 GMT

Thank you for your fast reply but maybe I should be more clear:

1.I have a line chart, displaying agg quantity of many Tanks with liquid (containers) which can be filtered by many list box like:

Type of material in container and\or country in which the Tanks located, Cites in which the tank located and set.

2. In addition, the user can filet (by list box) any period he likes: from a year\month\day list box (so he can pick 3 days or 482 days or every first of the month for 2014 and 2015 and so on. The data reflect in the line chart with the expression (as I wrote in the first post) : Aggr (Sum (TL1),[day/month/year],P1) where TL1=Liquid qty and P1=Liquid type.

3. I do not want to calculate max - min value but earliest's date value minus latest's date value.

I tried adjusting your express , looking for the earliest point with:

=Sum({<Day\Month\Year={$(=min(Day\Month\Year))}>}Aggr(Sum(TL1),Day\Month\Year)) but there is a script problem with {$(... it show the expression after the $ as a variable (in gray).

Thank you!

Nir

Re: Last selected value minus first selected value

Digvijay_Singh — Thu, 17 Sep 2015 09:04:54 GMT

If you can share sample data (even 1-2 sample rows) with all dimensions, I think I can make it work. Day\Month\Year is what? single date value or 3 different fields? invalid date formatting can also create issue. Somehow I feel it can be applied properly to identify min selected week(period) and max selected week(period) to get rid of issues. Set Analysis syntax is bit complex, even a small typo of ,{< here and there can create problem.

Re: Last selected value minus first selected value

Mon, 21 Sep 2015 08:07:59 GMT

Hello,

I got the answer, if any one else need it:

Sum({<Date={'$(=MIn(Date))'}>} Qty1+Qty2) - Sum({<Date={'$(=Max(Date))'}>} Qty1+Qty2)

Where Qty1+Qty2 is just an example to my complex aggregation expression.

In addition you need to define in the script the following:

date(date#(Date,'DD.MM.YY'),'DD/MM/YYYY') as Date,

I attach the Qlik and Data file which I created to explain my problem.

Good Luck and thank you all for your effort.

Nir