Need logic for distinct count of field in 1 week only

[shraddha_g]

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

[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]

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]

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 · Accepted solution]

May be this

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

When JAN is selected

When nothing is selected

[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), ', ')

[shraddha_g]

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

[shraddha_g]

Thank you Sunny!