Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
balabhaskarqlik
Honored Contributor

Re: Need logic for distinct count of field in 1 week only

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
Honored Contributor

Re: Need logic for distinct count of field in 1 week only

See these screenshots for your requirement:

Screenshot 1: Without selections.

Screenshot 2: with month selected as JAN.

Partner
Partner

Re: Need logic for distinct count of field in 1 week only

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

balabhaskarqlik
Honored Contributor

Re: Need logic for distinct count of field in 1 week only

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.

Partner
Partner

Re: Need logic for distinct count of field in 1 week only

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.

Highlighted

Re: Need logic for distinct count of field in 1 week only

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

Re: Need logic for distinct count of field in 1 week only

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

Partner
Partner

Re: Need logic for distinct count of field in 1 week only

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



Re: Need logic for distinct count of field in 1 week only

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

Partner
Partner

Re: Need logic for distinct count of field in 1 week only

Thank you Sunny!