topic Re: [set analysis] Get a value depending on a day in QlikView

[set analysis] Get a value depending on a day

Thu, 23 Jul 2015 13:29:28 GMT

Hello,

I have a problem I'm the solution is simple - but I just don't get it...

My Data: I have a set of InputDates, another set of days and temperatures.
On every InputDate there are 7 temperatures of 7 days entered. The range is InputDate-1 to InputDate+6.

I'm working in a pivot diagram. My dimension is Day.

So it looks something like that:

InputDate	Day	Temperatur
01.10.2014	30.09.2014	16.5
01.10.2014	01.10.2014	15.1
01.10.2014	02.10.2014	15.7
01.10.2014	03.10.2014	15.9
01.10.2014	04.10.2014	15.1
01.10.2014	05.10.2014	15.9
01.10.2014	06.10.2014	14.3
02.10.2014	01.10.2014	15.6
02.10.2014	02.10.2014	16.1
02.10.2014	03.10.2014	15.6
02.10.2014	04.10.2014	15.5
02.10.2014	05.10.2014	12.2
02.10.2014	06.10.2014	11.4
02.10.2014	07.10.2014	13.2
03.10.2014	02.10.2014	14.3
03.10.2014	03.10.2014	16.2
03.10.2014	04.10.2014	12.4
03.10.2014	05.10.2014	16.4
03.10.2014	06.10.2014	14.9
03.10.2014	07.10.2014	18.1
03.10.2014	08.10.2014	15.3

Now I want to display the average temperature over all InputDays for one Day. I.e.: 03.10.2014 (15.9 + 15.6 + 16.2)/3 = 15.9

I have following approach:

=(Sum({$<InputDate={$(=Date(Day))}>}temperature)+Sum({$<InputDate={$(=Date(Day-1))}>}temperature)+Sum({$<InputDate={$(=Date(Day-2))}>}temperature))/3

But nothing is displayed...

Re: [set analysis] Get a value depending on a day

Thu, 23 Jul 2015 13:43:35 GMT

I almost forgot: If I replace the $(=Date(Day)) with the actual term "03.10.2014" it works...

Re: [set analysis] Get a value depending on a day

sasiparupudi1 — Thu, 23 Jul 2015 13:58:23 GMT

Please try

day(date#('04.10.2014','DD.MM.YYYY'))

Re: [set analysis] Get a value depending on a day

antoniotiman — Thu, 23 Jul 2015 14:09:46 GMT

Hi Markus,

Try (e.g. in Pivot Table)

Dimensions : Day,InputDate

Expression

RangeAvg(Top(Temperatur,1,NoOfRows()))

Regards,

Antonio

Re: [set analysis] Get a value depending on a day

Thu, 23 Jul 2015 14:13:35 GMT

Hi Sasidhar,

thanks for the answer - but I don't want a fix date in the expression, the variable Day should be used.

Re: [set analysis] Get a value depending on a day

Thu, 23 Jul 2015 14:21:54 GMT

Hi Antonio,

your solution works, but I'm not quite happy with the layout. Now I have the additional InputDate dimension and therefore the right answer several times (for each InputDate). How can I extract the InputDate from the dimension into the expression layer?

Thanks for the help!

Regards

Markus

Re: [set analysis] Get a value depending on a day

antoniotiman — Thu, 23 Jul 2015 14:27:51 GMT

If You Remove InputDate,

expression is

Avg(Temperatur)

Re: [set analysis] Get a value depending on a day

sasiparupudi1 — Thu, 23 Jul 2015 14:36:37 GMT

Replace the date string with your day variable and it should work fine

hth

Re: [set analysis] Get a value depending on a day

sasiparupudi1 — Thu, 23 Jul 2015 18:14:21 GMT

Your expression should be

(Sum({$<InputDate={"$(=date#(Day,'DD.MM.YYYY'))"}>} temperature )+Sum({$<InputDate={"$(=date(date#(Day,'DD.MM.YYYY')-1,'DD.MM.YYYY'))"}>} temperature )+Sum({$<InputDate={"$(=date(date#(Day,'DD.MM.YYYY')-2,'DD.MM.YYYY'))"}>} temperature ))/3

Please see the attachement. Hope this helps