Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to find the top 3 Reason fields to put into a straight table by date and only if product is not blank; and also a further selection if the TYPE is A,B or C
So the table would look like
Date: Top 3 Reasons Rank
01/01/2007 Comment A 1
01/01/2007 Comment B 2
01/02/2007 Comment C 3
01/02/2007 Comment B 2
01/02/2007 Comment A 3
01/03/2007 Comment B 1
01/03/2007 Comment D 2
01/03/2007 Comment E 3
=AGGR(IF(RANK(COUNT({1<[TYPE]={'A','B','C'},[Product]={'=LEN([Reasons])>0'}>}[Product]))=2,Count([Product])),Month,[Product])
But I can't seem to return the text part of product, I only want to see the top 3 comments for each month.
How do I do this in Set Analysis?
Hi
Can u specify what the dimensions you are used?
if you didn't add the product as your dimension pls try it. let me know what you got the result
can you pls the provide the sample data?
Regards
Ero
Ok thanks I should have added it as a dimension and not an expression
try
am not sure
=FirstSortedValue(Top3Resons,-Date,3)
This my formula:
=AGGR(IF(RANK(COUNT({1<[OverallOutcome]={'Upheld'},[RootCauseComplaint]-={'*'}>} [RootCauseComplaint]),4)<4,[RootCauseComplaint]),Resolved_Month,[RootCauseComplaint])
But the set analysis is returning more than 'Upheld', this formula is in a dimension in straight table.
Why is my set not selecting upheld only?
From this what values you are expecting for UI
Hi,
You need only upheld values right. you have used a - sign in [rootcausecomplaint] which mean as of my knowledge it exclude the [overalloutcome] values which matches in [rootcausecomplaint ]
Try this:
=AGGR(IF(RANK(COUNT({1<[OverallOutcome]={'Upheld'},[RootCauseComplaint]={'*'}>} [RootCauseComplaint]),4)<4,[RootCauseComplaint]),Resolved_Month,[RootCauseComplaint])
Regards
Ero
Hi Eromiya,
I understood [RootCauseComplaint]-={'*'} to drop all null values in root cause complaints.
If I remove the statement from set analysis, I get other data items in Overall Outcome, I just want to see 'Upheld'
Hi,
i think there is no need of mention rootcausecomplaint inside the set analysis
then try dis:
=AGGR(IF(RANK(COUNT({1<[OverallOutcome]={'Upheld'}>} [RootCauseComplaint]),4)<4,[RootCauseComplaint]),Resolved_Month,[RootCauseComplaint])
Regards
Ero
I need to filter out blank entries.
My issue at the moment is selecting a set that is just Upheld cases and ranking just them.
If the set includes other cases then when calculating the top 3, their could be a case that is not 'upheld'.
Then I need to figure out how to do this by month.