New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Contributor

KPI: sum by Rank and Set analysis

Hi all
this's my first post.
I searched for this topic on other post but could't find a solution which works properly.
I hope to be clear enough and to find a solution.

I've a database with
- Project (some record may be blank (1 chr), some with values)
- Items
- Costs

I'm trying to create a KPI which displays the sum of the top 3 items order by Cost descending, ONLY on those records with:
- a Project which is valid (not blank),
- items like '10*'

It should be something similar to this
= Sum(
{< [Project Code] -= {" *"}
, [Item Code] = {"10*"}
>}
[Cost]
)
However this is not suitable because works on full database. I need to add a Rank

The problem is I can't get the sum of the restricted list

List Original (not sorted):
Project Item Cost
Prj_1 10400 30.157
4892 29.868
Prj_3 11563 23.851
20303 6.738
Prj_2 10562 9.868
Prj_4 10678 33.200

List sorted by Cost:
Project Item Cost
Prj_4 10678 33.200
Prj_1 10400 30.157
4892 29.868
Prj_3 11563 23.851
Prj_2 10562 9.868
20303 6.738

Top 3 (only related to project not blank, item like 10*)
Project Item Cost
Prj_4 10678 33.200
Prj_1 10400 30.157
Prj_2 10562 9.868

I want to get the total 73.225 (33.200 + 30.157 + 9.868)

I tried using all possible variations/combinations of Sum, Aggr, Rank, ... without success
The most similar good formula is

sum(Aggr(if(Rank(Sum(Cost),4,1) <=3, Sum(Cost), 0), Project_Item))

but it's not so good because I need to work on a set analysis. And if I enter a set analysis in it, it doesn't work

(futhermore, intstead of the number 3, I'll add a variable to let user to choose, with a slicer, it's own list of record to evaluate)

Did someone already occurred into same problem?
Does someone has any good idea?

Here below a prepared source code if someone wants to copy/past for his/her test

Project_Item_Cost:
Project,Item,Cost
Prj_1,10400,30.157
,4892,29.868
Prj_3,11563,23.851
,20303,6.738
Prj_2,10562,9.868
Prj_4,10678,33.200
];

1 Solution

Accepted Solutions
Contributor
Author

Thank you very much for your help.

It works properly.

My mistake was due to wrong usage of aggregation: I used [Project] & [Item] and I got display error.
Then I created a new concatenated field [Project_Item] in load script (load ... [Project] & [Item] AS [Project_Item]...) and now it works fine !

I post here below the final code:

Sum({<[Project] -= {''},

[Item]     = {"10*"}

>}

Aggr(

If(

Rank(

Sum({<[Project] -= {''},

[Item]     = {"10*"}

>}

[Cost])

, 4, 1) <= 3

, Sum({<[Project] -= {''},

[Item]     = {"10*"}

>}

[Cost])

, 0

)

, [Project_Item])

)

Bye

2 Replies
Partner - Champion III

Perhaps something like this:

``````Sum({<[Project Code] -= {''}, [Item Code] = {"10*"}>}
Aggr(
If(Rank(
Sum({<[Project Code] -= {''}, [Item Code] = {"10*"}>} Cost), 4, 1) <= 3,
Sum({<[Project Code] -= {''}, [Item Code] = {"10*"}>} Cost), 0
)
, Project_Item)
)``````

(Assumes that the excluded project codes are empty strings, if they are a single space, insert a space    ...-= {' '}...)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Contributor
Author

Thank you very much for your help.

It works properly.

My mistake was due to wrong usage of aggregation: I used [Project] & [Item] and I got display error.
Then I created a new concatenated field [Project_Item] in load script (load ... [Project] & [Item] AS [Project_Item]...) and now it works fine !

I post here below the final code:

Sum({<[Project] -= {''},

[Item]     = {"10*"}

>}

Aggr(

If(

Rank(

Sum({<[Project] -= {''},

[Item]     = {"10*"}

>}

[Cost])

, 4, 1) <= 3

, Sum({<[Project] -= {''},

[Item]     = {"10*"}

>}

[Cost])

, 0

)

, [Project_Item])

)

Bye

Tags
Community Browser