[set analysis] Get a value depending on a day

[#1]

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

[#2]

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

[sasiparupudi1]

Please try

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

[antoniotiman]

Hi Markus,

Try (e.g. in Pivot Table)

Dimensions : Day,InputDate

Expression

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

Regards,

Antonio

[#5]

Hi Sasidhar,

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

[#6]

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

[antoniotiman]

If You Remove InputDate,

expression is

Avg(Temperatur)

[sasiparupudi1]

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

hth

[sasiparupudi1 · Accepted solution]

Hi

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