Show the category based on variable

[ashishbhuyekar]

Hi All,

I want to show appropriate category based on TotalAvailable  & addition count variable.

Assumption is show the category for CountAcumulated is greater than equal TotalAvailable + Addional Count variable of first record.

Below are the tables -

ID	Total Available
94336	6
94337	4
94338	3

ID	Category	CountAcumulated	Path
94336	Available	12	6
94336	Enhancement	18	12
94336	Replacement	24	18
94337	Available	10	6
94337	Available	16	12
94337	Enhancement	22	18
94337	Change Required	24	24
94338	Enhancement	9	6
94338	Enhancement	15	12
94338	Change Required	21	18
94338	Replacement	24	24

I am expecting below result on bar chart –

1. For vAddition = 7 , output would be 2 Enhancement & 1 Available.
2. For vAddition = 13, output would be 2 Enhancement & 1 Change Required

But I am unable to get an expected result, can someone please help here? It seems that I am unable to aggregate the total available count on site level.

Bar chart -

Dimension – AGGR(FirstSortedValue({<CountAcumulated = {">=$(=[Total Available]+vAdditional)"}>}Category,Path),ID)

Expression - COUNT(distinct ID)

[Anil_Babu_Samineni]

Can you share QVW file?

[ashishbhuyekar]

Hi Anil,

Thanks for replying. I have already shared what is there in QVW. Please refer my post.

Thanks,

Ashish

[sunny_talwar · Accepted solution]

May be try this

Dimension

=Aggr(FirstSortedValue(If(CountAcumulated >=[Total Available] + vAddition, Category), If(CountAcumulated >=[Total Available] + vAddition, Path)),ID)

Expression

=Count(DISTINCT ID)

Also, I think your output based on vAddition = 13 should be 1 for Replacement, 1 for Enhancement and 1 for Change Required

[ashishbhuyekar]

Thanks Sunny for the quick resolution!!!