Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Santosh_K
Contributor III
Contributor III

Latest values for latest date based on selection

Hi All,

I need help on below scenario. I have data as below

LOAD * Inline [
Acct,Div,Date,Value
1,a,01/01/2021,100
1,b,01/02/2021,60
1,b,01/03/2021,80
2,b,01/01/2021,90
2,a,01/02/2021,50
2,a,01/03/2021,70]

Let's say I have selected month 01/03/2021 and Div is not selected I would always like to show latest Value for Acct. If for that period value is not available, need to go back to previous periods to select latest available Value. I am using below measure which is working fine in this case

FirstSortedValue(distinct {<Date ={"<=$(vMaxDate)"}>} Value, -Date)

where vMaxDate = max(Date)

Now, when I select month 01/03/2021 and Div = 'a' , for Acct =1, I would like to show value as 0, because for this Acct, latest Date has Div=b. Similarly, for Acct=2, I would like to show value as 70, because for this Acct, latest Date has Div=a

Acct   Value

1             0

2             70

In short, when I select any Div, particular Acct should only get value if that Acct's latest Date has that selected Div.

Similarly, when I select month 01/03/2021 and Div = 'b' ,  I should get below

Acct   Value

1             80

2             0

I am not able to get around the expression when particular Div is selected

1 Solution

Accepted Solutions
Kushal_Chawda

@Santosh_K  If I understood it correctly, you can try something like below. Make sure that "Include zero values" option is checked in add-on->data handling 

if(GetSelectedCount(Div)=0, FirstSortedValue(distinct {<Date={"<=$(=date(max(Date)))"}>}Value, - Date),
sum(distinct {<Date={"$(=date(max(Date)))"}>}Value))

View solution in original post

1 Reply
Kushal_Chawda

@Santosh_K  If I understood it correctly, you can try something like below. Make sure that "Include zero values" option is checked in add-on->data handling 

if(GetSelectedCount(Div)=0, FirstSortedValue(distinct {<Date={"<=$(=date(max(Date)))"}>}Value, - Date),
sum(distinct {<Date={"$(=date(max(Date)))"}>}Value))

View solution in original post