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

[patriciousa]

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.

Date1	Date2	Documents	Type	Header 5	Header 6
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

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.

[Qrishna]

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]

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 · Accepted solution]

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]

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]

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

[Qrishna]

check this out.

[Digvijay_Singh]

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.