topic Finding count with two conditions from associated tables. in App Development

Finding count with two conditions from associated tables.

Marcushenriquesk — Tue, 10 Aug 2021 16:38:41 GMT

I am attempting to create a graph for a app I created. The graph will display the amount of orders we have received that have a difference in its printed a recieved date <=2 as well if these orders contain the 'PANEL_IDS' 4000 or 9209.

Here are the statements:

Count(Distinct ACC_ID)

IF(WILDMATCH(PANELS.PANEL_ID, ''4000,'9209'), ACC_ID)

IF(INTERVAL(DATE(Printed_date, 'MM/DD/yyyy') - DATE(Received_date, 'MM/DD/yyyy'), 'DD')<=2, ACC_ID)

Both of these if conditions work separately however how do I combine them to make a count with 2 conditions.

Here was my attempt that has logic that makes sense to me but the code will not work, and the debugging in the expressions don't help me pinpoint the issue.

=Count(If(Interval(Date(Date#([PRINTED_DATE],'MM/DD/yyyy hh:mm:ss'),'MM/dd/yyyy') - Date(Date#([RECEIVED_DATE],'MM/dd/yyyy hh:mm:ss'),'MM/dd/yyyy'), 'DD' < =2, (WILDMATCH(PANELS.PANEL_ID, ''4000,'9209'), ACC_ID))

Re: Finding count with two conditions from associated tables.

Kushal_Chawda — Tue, 10 Aug 2021 21:48:26 GMT

@Marcushenriquesk try below

Count(If(Interval(Date(Date#([PRINTED_DATE],'MM/DD/yyyy hh:mm:ss'),'MM/dd/yyyy') - Date(Date#([RECEIVED_DATE],'MM/dd/yyyy hh:mm:ss'),'MM/dd/yyyy'), 'DD' < =2 and WILDMATCH(PANELS.PANEL_ID, '4000','9209'), ACC_ID))

Re: Finding count with two conditions from associated tables.

Marcushenriquesk — Wed, 11 Aug 2021 15:05:04 GMT

It says AND is not a value. I found the solution myself

=Count(DISTINCT if(Interval(Date([PRINTED_DATE],'MM/dd/yyyy') - Date([RECEIVED_DATE],'MM/dd/yyyy'), 'DD') <= 2, IF(WildMatch(PANEL_ID, '4000', '9209'), ACC_ID)))

I found that if conditions are cascading, so if there are two if statements. You declare the count and to be distinct and create the first if statement the next if statement is comma separated but still within the paracentesis of the first if statement. EXAMPLE, =Count(IF(CONDITION, IF(CONDITION, 'FIELD TO BE COUNTED')))