7 Replies Latest reply: Apr 9, 2018 5:04 PM by Digvijay Singh

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

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.

Kind regards.

• ###### 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])

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

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.

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

check this out.

• ###### 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

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

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

Inline

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]))

• ###### 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.

• ###### 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.

• ###### 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])