Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

patriciousa
Contributor

Count distinct if column contains specific value and sum is less than

Community, hi.

I been trying to insert a KPI that counts distinct the documents which are Type Q and where Date1-Date2 is less than 0.

Date1Date2DocumentsTypeHeader 5Header 6
15/07/20174/09/20173333333Q
4/05/20171/02/20173244443Q
19/02/201715/01/20179930303F
2/03/201714/03/20173333333Q

I have tried the following and I know why it does not work, but I can't seem to find a way to add the rest...

IF(SUM([Date1]-[Date2]<0), Count({<[Type]={"Q"}>} [Documents],))

This formula gave me the TOTAL of Documents Type Q.

Thank you in advance for your help.

Kind regards.

1 Solution

Accepted Solutions
sasiparupudi1
Honored Contributor III

Re: Count distinct if column contains specific value and sum is less than

Your dates look like strings..convert them to dates before using +/- operations on them

LOAD

Date(Date#(Date1,'DD/MM/YYYY'),'DD/MM/YYYY') as Date1,

Date(Date#(Date2,'DD/MM/YYYY'),'DD/MM/YYYY') as Date2,

Documents,Type,Header5

Inline

[Date1,Date2,Documents,Type,Header5,Header6

15/07/2017,4/09/2017,3333333,Q,,

4/05/2017,1/02/2017,3244443,Q,,

19/02/2017,15/01/2017,9930303,F,,

2/03/2017,14/03/2017,3333333,Q,

];

in the front end, you can use an expression like

Count({<[Type]={"Q"}>} if((Date1-Date2)<0,[Documents]))

or

if((Date1-Date2)<0,Count({<[Type]={"Q"}>} [Documents]))

7 Replies
krishna_2644
Valued Contributor III

Re: Count distinct if column contains specific value and sum is less than

Date1-Date2 is less than 0 - What does it mean? are you looking for something like 'If(Date1=Date2, Then , Else)' ?

Then probably you can try -

IF([Date1]=[Date2], Count({$<[Type]={'Q'}>}  distinct [Documents])


if you are trying to find the difference(assuming you have dates as nums) and checking if the difference is <0,then

IF([Date1]-[Date2]<0, Count({$<[Type]={'Q'}>}  distinct [Documents])

patriciousa
Contributor

Re: Count distinct if column contains specific value and sum is less than

Hi Krishna, thank you for your reply.

By Date1 - Date2 I'm trying to only count distinct the documents where Date1 > Date2

I did some changes to your second formula and it gives me the same total as mine, which counts the total of distinct documents type Q, it does not matter the dates. IF(SUM([Date1]-[Date2]<0), Count({<[Type]={"Q"}>} [Documents],))


Thank you.

sasiparupudi1
Honored Contributor III

Re: Count distinct if column contains specific value and sum is less than

Your dates look like strings..convert them to dates before using +/- operations on them

LOAD

Date(Date#(Date1,'DD/MM/YYYY'),'DD/MM/YYYY') as Date1,

Date(Date#(Date2,'DD/MM/YYYY'),'DD/MM/YYYY') as Date2,

Documents,Type,Header5

Inline

[Date1,Date2,Documents,Type,Header5,Header6

15/07/2017,4/09/2017,3333333,Q,,

4/05/2017,1/02/2017,3244443,Q,,

19/02/2017,15/01/2017,9930303,F,,

2/03/2017,14/03/2017,3333333,Q,

];

in the front end, you can use an expression like

Count({<[Type]={"Q"}>} if((Date1-Date2)<0,[Documents]))

or

if((Date1-Date2)<0,Count({<[Type]={"Q"}>} [Documents]))

Digvijay_Singh
Honored Contributor III

Re: Count distinct if column contains specific value and sum is less than

I agree on the fact that date must be treated as date type, also may be expression are like this -

Count({<[Type]={'Q'},Date1={"=Date1<Date2"}>}[Documents])

patriciousa
Contributor

Re: Count distinct if column contains specific value and sum is less than

Thank you there, working now with if((Date1-Date2)<0,Count({<[Type]={"Q"}>} [Documents])), the first one gives me null values tho.


krishna_2644
Valued Contributor III

Re: Count distinct if column contains specific value and sum is less than

check this out.

1.PNG

Digvijay_Singh
Honored Contributor III

Re: Count distinct if column contains specific value and sum is less than

I think 'if' should be written inside Count function, may be I misunderstood but you may think of verifying once again. Naked field names should be avoided in measures. field names must always be inside aggregation functions in almost all situations.

Community Browser