Announcements
cancel
Showing results for
Did you mean:
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

1 Solution

Accepted Solutions
MVP

May be this

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

When JAN is selected

When nothing is selected

19 Replies
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,',')

Partner - Master III
Author

It should be count of brand sold..

Creator II

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

Is it possible to provide your app?

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

Master

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)

Master

May be this:

Create Week list box,

This expr in pivot table.

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

Partner - Master III
Author

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

Update: - Updated Output

Master

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

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