Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]))
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])
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.
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]))
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])
Thank you there, working now with if((Date1-Date2)<0,Count({<[Type]={"Q"}>} [Documents])), the first one gives me null values tho.
check this out.
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.