Aggr Count result on every rows

[rakeshkumar1890]

Hello Everyone,

Need your help on the below output:

Raw Data:

Category	Part	Order	Description	Item
Qix	100	2001	Webinar	7
Mri	100	2001	Webinar	7
Qix	100	2001	Market	7
Qix	100	2002	Webinar	7
Mri	100	2003	Webinar	7
Mri	100	2003	Market	7
Wex	200	575	Webinar	18
Wex	200	575	Market	18
Wex	200	575	Campaign	18
Wex	200	575	Campaign	18
Wex	200	760	Market	12
Wex	200	760	Market	12
Wex	200	760	Campaign	12

Output Table:

Category	Part	Description	Item	Total
Mri	100	Webinar	7	3
Mri	100	Market	7	3
Qix	100	Webinar	7	3
Qix	100	Market	7	3
Wex	200	Market	12	1
Wex	200	Campaign	12	1
Wex	200	Webinar	18	1
Wex	200	Market	18	1
Wex	200	Campaign	18	1

To find out "Total" -- Need to group by Part, Order, Item then Count(Item)

I am using this expression but not getting result on every rows :

Expression : count(aggr(count(Item), Part, Order))

This is not showing exactly the above Output table result. --- I need this result on every rows on Total Column.

[Taoufiq_Zarra]

@rakeshkumar1890  in script or in chart ? and how you get for example this line ?

Wex

200

Campaign

18

1

[rakeshkumar1890]

@Taoufiq_Zarra  I need on chart and It should be distinct by Part, Order and Item

[Brett_Bleess]

I would venture you may need both Set Analysis and AGGR in this case, here are a couple of Design Blog posts that may be helpful:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

Hopefully those may help.

Regards,
Brett

[Kushal_Chawda]

@rakeshkumar1890  you can go other way around by counting orders instead

=sum(total <Part,Item>aggr(Count(DISTINCT Order),Part,Item))

or

=Count(DISTINCT total <Part,Item>Order)