Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
shraddha_g
Partner - Master III
Partner - Master III

Need logic for distinct count of field in 1 week only

Dear All,

We have to build a logic to count of brand sold particular month/Year.

however, we have to display this data w,r,t Week.

If one brand is sold in 1st week then it should not be counted again in another week of that month.

And this logic should work as per selection of Month/Year.

Sample Data:

Brand, Week, Area, Zone, Division, Month, Year

A, 1, Area1, Zone1, D1, JAN, 2018

A, 2, Area1, Zone1, D1, JAN, 2018

B, 2, Area1, Zone1, D1, FEB, 2018

B, 3, Area1, Zone1, D1, FEB, 2018

C, 1, Area1, Zone1, D1, JAN, 2018

C, 4, Area1, Zone1, D1, FEB, 2018

A, 4, Area1, Zone1, D1, MAR, 2018



stalwar1


Thanks in advance


1 Solution

Accepted Solutions
sunny_talwar

May be this

Sum(Aggr(If(Brand <> Above(TOTAL Brand), 1, 0), Zone, Area, Brand, Week))

When JAN is selected

Capture.PNG

When nothing is selected

Capture.PNG

View solution in original post

19 Replies
rangam_s
Creator II
Creator II

Hi stalwar1,

How do you want to see this? if you want to see this as a Table then add Week as Dimension (in Pivot table)

Expression:

Concat(distinct Brand,',')

shraddha_g
Partner - Master III
Partner - Master III
Author

It should be count of brand sold..

rangam_s
Creator II
Creator II

In your table there is no information on whether it is sold or not sold.

Is it possible to provide your app?

shraddha_g
Partner - Master III
Partner - Master III
Author

Dear All,

We have to build a logic to count of brand sold particular month/Year.

however, we have to display this data w,r,t Week.

If one brand is sold in 1st week then it should not be counted again in another week of that month.

here i have mentioned Sold Brand

balabhaskarqlik

May be like this:

Pivot table -

Dimensions: Other dimensions, week.

Expression: Brands sold on that week.

Count({ <Week = {"$(=Max(Week))"}> }Brand)

Or

Aggr(Count({ <Week = {"$(=Max(Week))"}> }Brand),Brand)

balabhaskarqlik

May be this:

Create Week list box,

This expr in pivot table.

Count({ <Week = {'$(=getfieldselections(Week))'}> }Brand)

shraddha_g
Partner - Master III
Partner - Master III
Author

My expected output on selection month Jan should be as in attached Image

Update: - Updated Output

balabhaskarqlik

Yes above expr's specified based on Week number.. what's the result you are getting.

Count({ <Week = {'$(=getfieldselections(Week))'}, Month = {'$(=getfieldselections(Month))'} > }Brand)

Add: Zone, Area as Dimensions.

shraddha_g
Partner - Master III
Partner - Master III
Author

we cant consider week selection in set analysis.

there is Month/Year selection.

It should be min week always for any selection.

and column dimension will be Week in Pivot.