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

[rangam_s]

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]

It should be count of brand sold..

[rangam_s]

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

Is it possible to provide your app?

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

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]

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]

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.