Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
gkouris77
New Contributor II

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.

Capture.PNG

Thanks

George

10 Replies
Highlighted
MK9885
Honored Contributor II

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?

Highlighted
Partner
Partner

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

Highlighted
gkouris77
New Contributor II

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

Highlighted

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
gkouris77
New Contributor II

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

Highlighted

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
gkouris77
New Contributor II

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

Highlighted

Re: Select a Cost at a point of time

As Much As simple like

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
gkouris77
New Contributor II

Re: Select a Cost at a point of time

Hi Anil,

i still get a zero value.

thanks

george