topic Re: How to use FirstSortedValue() to aggregate 2 or more values in App Development

How to use FirstSortedValue() to aggregate 2 or more values

Umeiko — Mon, 08 Jan 2024 07:28:39 GMT

I load CustProjectData script (on QlikSense Desktop) as belows：

===============================

CustProjectData:

LOAD * inline [
CustName|Project|DataYrMon|Backlog
Astrida|AA|202311|1131313
Astrida|AA|202312|1141233
Astrida|BB|202311|14152
Astrida|BB|202310|14122
Betacab|CC|202311|101111
Betacab|CC|202312|122222
Betacab|CC|202310|222222
Betacab|CC|202309|201111
Betacab|DD|202312|250000
Canutility|EE|202312|15000
Canutility|EE|202310|11100
Canutility|FF|202310|19000
] (delimiter is '|');

=================================

I am developing a table as belows, I use

firstsortedvalue (Distinct Backlog, -DataYrMon)

to get the lastest year-month's backlog value, and it's correct.

Now I need to aggregate the same CustName's measures together as noted on the following picture.

How do I do to get the correct caculations?

Please help and many thanks.

Re: How to use FirstSortedValue() to aggregate 2 or more values

marcus_sommer — Mon, 08 Jan 2024 08:55:15 GMT

Try it with:

sum(aggr(firstsortedvalue (Distinct Backlog, -DataYrMon), CustName))

Re: How to use FirstSortedValue() to aggregate 2 or more values

Umeiko — Tue, 09 Jan 2024 00:06:13 GMT

I tried the caculation but it doesn't work, it shows as the most right column...

Re: How to use FirstSortedValue() to aggregate 2 or more values

marcus_sommer — Tue, 09 Jan 2024 07:31:09 GMT

Try it with extending the calculation with:

sum(aggr(nodistinct firstsortedvalue (Distinct Backlog, -DataYrMon), CustName))

sum(total <CustName> aggr(firstsortedvalue (Distinct Backlog, -DataYrMon), CustName))

Re: How to use FirstSortedValue() to aggregate 2 or more values

Umeiko — Thu, 11 Jan 2024 02:40:23 GMT

Thanks. however, I tried both caculations, they don't work as expecting...

The first one(red column) seems use the first Backlog value multiplys by project counts and repeat,

and the second one (blue column) seems just repeat the first Backlog value.

both caculations don't meet requirements.

Re: How to use FirstSortedValue() to aggregate 2 or more values

marcus_sommer — Tue, 09 Jan 2024 11:33:58 GMT

Maybe adding Project to aggr() like:

sum(total <CustName> aggr(firstsortedvalue (Distinct Backlog, -DataYrMon), CustName, Project))

Re: How to use FirstSortedValue() to aggregate 2 or more values

Umeiko — Thu, 11 Jan 2024 01:29:14 GMT

Many many thanks!! It works