Latest of the getfieldselection

[qwerty89]

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.

@sunny_talwar @tresesco 

[sunny_talwar]

What will be displayed if Jan18 is selected with the above sample?

[qwerty89]

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 

[qwerty89]

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 

[sunny_talwar]

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.

[qwerty89]

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. 

[sunny_talwar]

Is this QlikView or Qlik Sense?

[qwerty89]

Qliksense

[sunny_talwar · Accepted solution]

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))

[qwerty89]

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.