Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 –
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)
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
Can you share QVW file?
Hi Anil,
Thanks for replying. I have already shared what is there in QVW. Please refer my post.
Thanks,
Ashish
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
Thanks Sunny for the quick resolution!!!