topic Show the category based on variable in QlikView

Show the category based on variable

ashishbhuyekar — Wed, 25 Nov 2020 16:16:04 GMT

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 –

For vAddition = 7 , output would be 2 Enhancement & 1 Available.
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)

Re: Show the category based on variable

Anil_Babu_Samineni — Tue, 31 Jul 2018 03:56:26 GMT

Can you share QVW file?

Re: Show the category based on variable

ashishbhuyekar — Tue, 31 Jul 2018 08:35:08 GMT

Hi Anil,

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

Thanks,

Ashish

Re: Show the category based on variable

sunny_talwar — Tue, 31 Jul 2018 11:08:27 GMT

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

Re: Show the category based on variable

ashishbhuyekar — Tue, 31 Jul 2018 11:27:16 GMT

Thanks Sunny for the quick resolution!!!