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: 
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


19 Replies
balabhaskarqlik

Yes for the above requirement, Use this expression in pivot table:

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

Label as - Count.

Use Dimensions: Zone, Area, Week.

balabhaskarqlik

See these screenshots for your requirement:

Screenshot 1: Without selections.

Screenshot 2: with month selected as JAN.

shraddha_g
Partner - Master III
Partner - Master III
Author

Here, if one brand is sold in 1st week then same brand should not be counted in 2nd or 3rd or 4th week

balabhaskarqlik

Yes, Result is same. Generated result screenshot 2 is as per your attachment pertains JAN MONTH result.

Because, i used WEEK as Dimension, so in Column4 (Count), it'll represents the Brand count of that week only.

Are you getting any issue in your object?

Post the screenshot.

shraddha_g
Partner - Master III
Partner - Master III
Author

Sorry By mistake, I uploaded wrong expected Output.

Please refer to the updated one.

if one brand is sold in 2 weeks in a month, then that brand should only get counted in 1st week only.

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

sunny_talwar

To see which brands make up the count, you can try this

Concat(Aggr(If(Brand <> Above(TOTAL Brand), Brand), Zone, Area, Brand, Week), ', ')


Capture.PNG

shraddha_g
Partner - Master III
Partner - Master III
Author

Thanks Sunny,

As usual, You are the Savior.

It works.

Now I want to add mote parameter into this.

I want to Consider those Brands who have done Sales > 0.

Where should I add that condition in the expression.

Field Name - [Sales Amount]

Sample Data:

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

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

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

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

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

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

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

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



sunny_talwar

Should be just this I guess

Sum(Aggr(If(Only({<[Sales Amount] = {">0"}>}Brand) <> Above(TOTAL Only({<[Sales Amount] = {">0"}>}Brand)), 1, 0), Zone, Area, Brand, Week))


Capture.PNG

shraddha_g
Partner - Master III
Partner - Master III
Author

Thank you Sunny!