- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to use FirstSortedValue() to aggregate 2 or more values
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe adding Project to aggr() like:
sum(total <CustName> aggr(firstsortedvalue (Distinct Backlog, -DataYrMon), CustName, Project))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try it with:
sum(aggr(firstsortedvalue (Distinct Backlog, -DataYrMon), CustName))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I tried the caculation but it doesn't work, it shows as the most right column...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try it with extending the calculation with:
sum(aggr(nodistinct firstsortedvalue (Distinct Backlog, -DataYrMon), CustName))
or
sum(total <CustName> aggr(firstsortedvalue (Distinct Backlog, -DataYrMon), CustName))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe adding Project to aggr() like:
sum(total <CustName> aggr(firstsortedvalue (Distinct Backlog, -DataYrMon), CustName, Project))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Many many thanks!! It works