8 Replies Latest reply: Oct 30, 2018 9:38 AM by Anil Samineni

# Select a Cost at a point of time

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

• ###### Re: Select a Cost at a point of time

Can you give us a sample of data in xl?

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

• ###### Re: Select a Cost at a point of time

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

• ###### Re: Select a Cost at a point of time

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

• ###### Re: Select a Cost at a point of time

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)

• ###### Re: Select a Cost at a point of time

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

• ###### Re: Select a Cost at a point of time

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)

• ###### Re: Select a Cost at a point of time

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

• ###### Re: Select a Cost at a point of time

As Much As simple like

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

• ###### Re: Select a Cost at a point of time

Hi Anil,

i still get a zero value.

thanks

george

• ###### Re: Select a Cost at a point of time

This is my last attempt from me?

Script looks like?

Peek(TransDate) as Previous,

AverageCost

FROM

(ooxml, embedded labels, table is Sheet1);

And Measure either of the below?

FirstSortedValue(AverageCost, -Aggr(Sum({<TransDate = P(Previous)>} AverageCost), Previous))

Or

Sum({<TransDate = P(Previous)>} AverageCost)