Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a filter for month selection in my app and each month has its own data entry.
Eg:-
ID Month Service_Status Payment_Cumulative
1 Jan18 Closed $100
1 Feb18 Open $200
1 Mar18 Closed $300
If I select month as Jan18 and Feb18 in the filter, I want to display the payment in the 'Open' service status KPI only(for the latest status) i.e. 200 at the ID level.
Try these
Sum(Aggr(If(FirstSortedValue(Service_Status, -Month) = 'Open', FirstSortedValue(Aggr(Sum(Payment_Cumulative), ID, Month), -Aggr(Month, ID, Month))), ID))
and
Sum(Aggr(If(FirstSortedValue(Service_Status, -Month) = 'Closed', FirstSortedValue(Aggr(Sum(Payment_Cumulative), ID, Month), -Aggr(Month, ID, Month))), ID))
What will be displayed if Jan18 is selected with the above sample?
If Jan'18 is selected it should display $100 in the closed service KPI.
Just when multiple years are selected in the filter the latest entry(amongst the selected) should be considered.
Like all the Jan18 ,Feb18 and Mar'18 are selected it should display $300 in the closed KPI (latest service status and latest payment payment) at the ID level
Just adding some more data to give more clarity on the query
ID Month Service_Status Payment_Cumulative
1 Jan18 Closed $100
1 Feb18 Open $200
1 Mar18 Closed $300
2 Jan18 Closed $150
2 Mar18 Open $400
If Feb18 and Mar18 are selected in the filters then following values are to be shown
Open Service Status Payments = $400
Closed Service Payments = $300
The above metrics are summed up after finding the values at latest transaction on ID level
May be use this
FirstSortedValue(Payment_Cumulative, -Month)
I don't know your complete dataset, so difficult to give a more comprehensive expression, but the above may not be enough you might need to use Aggr() function to make it work.
The data is similar to what I have shared above recently.
I do think so I may have to use aggr.
The thing is that I have to segregate the amount in Open and Closed service status based on the latest service status(this depends on the months selected).
Please let me know if you can help me more here.
It would be highly appreciated.
Is this QlikView or Qlik Sense?
Qliksense
Try these
Sum(Aggr(If(FirstSortedValue(Service_Status, -Month) = 'Open', FirstSortedValue(Aggr(Sum(Payment_Cumulative), ID, Month), -Aggr(Month, ID, Month))), ID))
and
Sum(Aggr(If(FirstSortedValue(Service_Status, -Month) = 'Closed', FirstSortedValue(Aggr(Sum(Payment_Cumulative), ID, Month), -Aggr(Month, ID, Month))), ID))
Excellent!!!
It is working perfectly for the required scenario.
Will let you if I have add some tweaks to it for different scenarios.
Thanks a ton for the help.
Highly appreciated.