Count If with Multiple Conditions

[Anonymous]

Hi,

Newbie to QV. I have the following expression for a chart; but I keep getting "Error in set modifier expression"

COUNT ({< [Issue ID]= {'Pending Issue ID'},  (((Date(now(),'MM/DD/YYYY')) - Date([Report Date], 'MM/DD/YYYY')) = {"<100"}) >} [Issue ID] )  

Help! Thanks in advance.

[vishsaggi]

you cannot use

(((Date(now(),'MM/DD/YYYY')) - Date([Report Date], 'MM/DD/YYYY')) = {"<100"})

like this in your set. Can you elaborate what exactly you are trying to filter here for <100.

[vishsaggi · Accepted solution]

may be one way you can do like below

CntCheck:
LOAD *, Num(Date(Today(),'MM/DD/YYYY') - Date(ReportDate, 'MM/DD/YYYY'), '#,##0') AS ReportDays;
LOAD *, Date(Date#(DateReported, 'MM/DD/YYYY'),'MM/DD/YYYY') AS ReportDate;
LOAD ISSUE ID, DateReported

FROM YOURSOURCE;

Then you can use like

= COUNT ({< [Issue ID]= {'Pending Issue ID'},  ReportDays = {'<100'}) >} [Issue ID] )

This is a dummy sample I made.

CntCheck:
LOAD *, Num(Date(Today(),'MM/DD/YYYY') - Date(ReportDate, 'MM/DD/YYYY'), '#,##0') AS ReportDays;
LOAD *, Date(Date#(DateReported, 'MM/DD/YYYY'),'MM/DD/YYYY') AS ReportDate INLINE [
ID, DateReported
1, 03/02/2018
2, 04/02/2018
2, 01/02/2018
2, 01/05/2018
2, 05/05/2018
];

= Count({< ID = {2}, ReportDays = {'<100'} >} ID)

[Digvijay_Singh]

You may try this -

COUNT ({< [Issue ID]= {'Pending Issue ID'}>*<[Issue ID]={"=Date(floor(today()),'MM/DD/YYYY') - Date([Report Date], 'MM/DD/YYYY')) <100"} >} [Issue ID] ) 

[Anonymous]

Thank you! It worked!!