topic Distinct Count in QlikView

Distinct Count

Thu, 16 Jul 2015 06:26:14 GMT

ACT_DATE	MAR	ALT_ACTN_USER_ID
2-Jun-15	150529SCBLMYKXBLAB0864192134	1487822
2-Jun-15	150529SCBLMYKXBLAB0864192134	1487822
2-Jun-15	150529SCBLMYKXBLAB0864192134	1487822
2-Jun-15	150529SCBLMYKXBLAB0864192134	1487822
2-Jun-15	150529SCBLMYKXBLAB0864192134	1487822
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487821
3-Jun-15	150603SCBLMYKXALAB0129875213	1487823
3-Jun-15	150603SCBLMYKXALAB0129875213	1487823
3-Jun-15	150603SCBLMYKXALAB0129875213	1487823
3-Jun-15	150603SCBLMYKXALAB0129875213	1487823
3-Jun-15	150603SCBLMYKXALAB0129875213	1487823
3-Jun-15	150603SCBLMYKXALAB0129875213	1487823
3-Jun-15	150603SCBLMYKXBLAB0865198847	1487823
4-Jun-15	150604SCBLMYKXXLAB0403702655	1487823
4-Jun-15	150604SCBLMYKXALAB0129876938	1487823
4-Jun-15	150604SCBLMYKXALAB0129876938	1487823
4-Jun-15	150604SCBLMYKXALAB0129876938	1487823
2-Jun-15	150529SCBLMYKXBLAB08641921355	1487822
2-Jun-15	150529SCBLMYKXBLAB08641921355	1487822
2-Jun-15	150529SCBLMYKXBLAB08641921355	1487822
2-Jun-15	150529SCBLMYKXBLAB08641921355	1487822
2-Jun-15	150529SCBLMYKXBLAB08641921355	1487822
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	150529SCBLMYKXBLAB086419	1487821
3-Jun-15	1AB08641921355	1487823
3-Jun-15	1AB08641921355	1487823
3-Jun-15	1AB08641921355	1487823
3-Jun-15	1AB08641921355	1487823
3-Jun-15	1AB08641921355	1487823
3-Jun-15	1AB08641921355	1487823
3-Jun-15	1AB08641921355	1487823
4-Jun-15	1AB08641921355	1487823
4-Jun-15	1AB08641921355	1487823
4-Jun-15	1AB08641921355	1487823
4-Jun-15	1AB08641921355	1487823

In the above table ,

Need to find for given ALT_ACTION_USRID , count of Unique mar greater than 2

Re: Distinct Count

PrashantSangle — Thu, 16 Jul 2015 06:30:09 GMT

hI,

Create straight/Pivot table

Take

ALT_ACTION_USRID as Dimension

and

Count(Distinct mar) as expression

Regards

Re: Distinct Count

Kushal_Chawda — Thu, 16 Jul 2015 06:33:28 GMT

Create straight table

Dimension : ALT_ACTN_USER_ID

Expression :count({<MAR= {"=count(MAR)>2"}>}MAR)

Re: Distinct Count

Thu, 16 Jul 2015 06:33:33 GMT

Straight table

Dimension -> ALT_ACTN_USER_ID

Expression -> If(Count(DISTINCT MAR)>2,Count(DISTINCT MAR))

Regards,

KKR

Re: Distinct Count

manojkulkarni — Thu, 16 Jul 2015 06:37:35 GMT

Take

ALT_ACTION_USRID as Dimension

and

Expression would be --count(distinct if(aggr(count(MAR),ALT_ACTN_USER_ID,MAR)>2,MAR))

Re: Distinct Count

qlikmsg4u — Thu, 16 Jul 2015 06:38:00 GMT

Hi Senthil

Please find the attached app

Re: Distinct Count

Thu, 16 Jul 2015 12:26:16 GMT

The output was not correct.

Requirement is No of Days , where the COunt(DISTINCT MAR) > 2

Re: Distinct Count

sasiparupudi1 — Thu, 16 Jul 2015 13:38:57 GMT

What do you mean by no of days? count of the act_date? it would be helpful if you could post some sample document or a more detailed requirement

Re: Distinct Count

_rohitgharat — Fri, 17 Jul 2015 11:18:11 GMT

Hi senthil,

First of all make sure what is your requirement and what you want to display. 'No of Days , where the Count(DISTINCT MAR) > 2' doesn't make any sense or may be it seems clueless. I think requirement may be to calculate count (MAR) pending since no. of days.

Thanks,

Rohit