Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to count the number of audit that meet the following criteria:
My current expression is as follows:
=Count({<[Internal Audits.Status]-={'Closed'}, [Internal Audits.Class]={'Internal'}>} [Internal Audits.Audit No])
How do I include the part where the Intended Completion date is less than today's (or current) date?
Jeff
=Count({<
[Internal Audits.Status]-={'Closed'},
[Internal Audits.Class]={'Internal'},
[Intended Completion] = {'<$(=Today())'}
>} [Internal Audits.Audit No])
=Count({<
[Internal Audits.Status]-={'Closed'},
[Internal Audits.Class]={'Internal'},
[Intended Completion] = {'<$(=Today())'}
>} [Internal Audits.Audit No])
Try this
Count({<[Internal Audits.Status]-={'Closed'}* [Internal Audits.Class]={'Internal'}*
CompletionDate={'<$(=date(today()))'}>} [Internal Audits.Audit No])
check out the format of CompletionDate is same as Date(Today()) format
hope it helps
Hi,
Try this
=Count({<[Internal Audits.Status]-={'Closed'}, [Internal Audits.Class]={'Internal'},
CompletedDate={'<$(=Today())'}>} [Internal Audits.Audit No])
Replace CompletedDate with your dimension name. If it is not working then check date formats of CompletedDate values and Today() are in the same format, if not then convert the format of Today() by using
Date(Today(), 'MM/DD/YYYY') - Replace MM/DD/YYYY with your date format.
Regards,
jagan.
Jagan:
The exact expression I have there now is:
=Count({<
[Internal Audits.Status]-={'Closed'},
[Internal Audits.Class]={'Internal'},
[Internal Audits.Intended Completion] = {'<$(=Date(Today(), 'MM/DD/YYYY')))'}
>} [Internal Audits.Audit No])
When I execute the number I get doesn't match the number I get when I run the qurey in MS Access (where my data is). I get 247 and it should be 6.
Jeff
Remove one closing parenthesis from : 'MM/DD/YYYY')))' and make it : 'MM/DD/YYYY'))'
i think you have to add Distinct over there....add it...
Try This....
=Count( Distinct {<
[Internal Audits.Status]-={'Closed'},
[Internal Audits.Class]={'Internal'},
[Internal Audits.Intended Completion] = {'<$(=Date(Today(),'MM/DD/YYYY'))'}
>} [Internal Audits.Audit No])
If still not working, we request you to upload your sample file....
I figured out the problem. I had the incorrect field named called out. It is actually "Intended Completion Date".
I appreciate the help from all of you, and I still needed the help with adding that part to the expression.
Thanks to all of you.
Jeff