Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Load * Inline [
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
];
Thanks in advance for your time
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
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 ...-= {' '}...)
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