Select a Cost at a point of time

[gkouris77]

Hi, i am new to Qlik Sense and I am trying to select a cost in point of time, and i cant seem to get the correct expression.

So i have a list Average Cost, and what i need is based on the Selected date i want to return the max date average cost that is equal to or less to the selected date. I have tried the below, expression but i cannot get it to work.

=if($(vSelectDate)=<[TransDate],max([TransDate],[TransDate]=<$(vSelectDate)),[TransDate])

Any help would be appreciated.

Thanks

George

[MK9885]

Can you give us a sample of data in xl?

And explain with if a Data is selected what number you expecting?

[dplr-rn]

As shahbaz said giving some sample data and expected output would help.

Assuming you want average cost where transaction date is less than or equal to selected date. You can use something like below

avg({<[TransDate]= {"<=$(vSelectDate)"} >}Cost)

ensure vSelectDate format is date and formatted same as TransDate

[gkouris77]

Hi

What im trying to actually achieve is if i select 30/9/2018 i would expect to see average cost as 34.17 which is from 28/9, and if i select 5/10 i would expect to see 34.12, and also if i select 11/10 then i would see 34.11. Attached is the excel.

thanks

George

[Anil_Babu_Samineni]

I don't see any thing related this? 30/9/2018

But, What i understand here if you want to go back 2 Days from selected, If so please try this?

Sum({<TransDate = {">=$(=Date(Max(TransDate-2)))<=$(=Date(Max(TransDate)))"}>} AverageCost)

[gkouris77]

Hi Anil,

i need to return the most recent Cost regardless of the date i pick. So if i do pick 30/9/18 it will provide the most up to date cost equal to or less to the selected date.

thanks

george

[Anil_Babu_Samineni]

I think this work, If that is the case?

Sum({<TransDate = {"<=$(=Date(Max(TransDate),'MM/DD/YYYY'))"}>} AverageCost)

Or

Sum({<TransDate = {"$('<=' & =Date(Max(TransDate),'MM/DD/YYYY'))"}>} AverageCost)

[gkouris77]

Hi,

Still not correct. i have 30/09/2018 selected. I should get the Average cost closest to that date, in this case its 28/09/18

But if i select i select 9/10/2018 i should get the average cost from the 8/10/2018.

thnaks

george

[Anil_Babu_Samineni]

As Much As simple like

Avg{<TransDate = {"$(=Date(Max(TransDate-1),'MM/DD/YYYY'))"}>} AverageCost)

[gkouris77]

Hi Anil,

i still get a zero value.

thanks

george