Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

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


1 Solution

Accepted Solutions

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

19 Replies
Highlighted
rangam_s
Contributor II

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

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
Partner

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

It should be count of brand sold..

rangam_s
Contributor II

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

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

Is it possible to provide your app?

Partner
Partner

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

here i have mentioned Sold Brand

balabhaskarqlik
Honored Contributor

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

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

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

May be this:

Create Week list box,

This expr in pivot table.

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

Partner
Partner

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

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

Update: - Updated Output

balabhaskarqlik
Honored Contributor

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

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.

Partner
Partner

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

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.