topic Re: Latest of the getfieldselection in App Development

Latest of the getfieldselection

qwerty89 — Fri, 05 Apr 2019 08:31:19 GMT

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 @tresB

Re: Latest of the getfieldselection

sunny_talwar — Fri, 05 Apr 2019 11:09:43 GMT

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

Re: Latest of the getfieldselection

qwerty89 — Fri, 05 Apr 2019 11:26:51 GMT

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

Re: Latest of the getfieldselection

qwerty89 — Fri, 05 Apr 2019 12:14:21 GMT

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

Re: Latest of the getfieldselection

sunny_talwar — Fri, 05 Apr 2019 12:15:30 GMT

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.

Re: Latest of the getfieldselection

qwerty89 — Fri, 05 Apr 2019 12:22:47 GMT

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.

Re: Latest of the getfieldselection

sunny_talwar — Fri, 05 Apr 2019 12:27:50 GMT

Is this QlikView or Qlik Sense?

Re: Latest of the getfieldselection

qwerty89 — Fri, 05 Apr 2019 12:30:44 GMT

Qliksense

Re: Latest of the getfieldselection

sunny_talwar — Fri, 05 Apr 2019 12:45:56 GMT

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

Re: Latest of the getfieldselection

qwerty89 — Fri, 05 Apr 2019 13:04:12 GMT

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.

Re: Latest of the getfieldselection

qwerty89 — Sat, 06 Apr 2019 13:54:37 GMT

Hi @sunny_talwar ,

Please find below the data for the query.I have to add a key in the data as well. Now, I want to find the payments for latest service status at key and ID level based on the month selection.

ID Month Service_Status Key Payment_Cumulative

1 Jan18 Closed A $100

1 Feb18 Open B $200

1 Mar18 Closed B $300

2 Jan18 Open C $150

2 Mar18 Open C $400

2 Jun18 Closed C $500

Example:-

If I select Jan18, Feb18 and Mar18 in the month filter

then the

Payments for Closed service status = $100(for ID 1 and latest service status of key A) + $300(ID 1 and latest service status of key B)

Payments for Open service status = $400(for ID 2 and latest service status of key C)

Any help here would be commendable.

@tresB