Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rakeshkumar1890
Creator
Creator

Aggr Count result on every rows

Hello Everyone,

Need your help on the below output:

Raw Data:

CategoryPartOrderDescriptionItem
Qix1002001Webinar7
Mri1002001Webinar7
Qix1002001Market7
Qix1002002Webinar7
Mri1002003Webinar7
Mri1002003Market7
Wex200575Webinar18
Wex200575Market18
Wex200575Campaign18
Wex200575Campaign18
Wex200760Market12
Wex200760Market12
Wex200760Campaign12

 

Output Table:

CategoryPartDescriptionItemTotal
Mri100Webinar73
Mri100Market73
Qix100Webinar73
Qix100Market73
Wex200Market121
Wex200Campaign121
Wex200Webinar181
Wex200Market181
Wex200Campaign181

 

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.

4 Replies
Taoufiq_Zarra

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

Wex200Campaign181
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
rakeshkumar1890
Creator
Creator
Author

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

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
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)

 

Screenshot 2020-10-27 223652.png