Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gkouris77
Contributor II
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
MK9885
Master II
Master II

Can you give us a sample of data in xl?

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

dplr-rn
Partner - Master III
Partner - Master III

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
Contributor II
Contributor II
Author

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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
Contributor II
Contributor II
Author

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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
Contributor II
Contributor II
Author

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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
Contributor II
Contributor II
Author

Hi Anil,

i still get a zero value.

thanks

george