Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Marcushenriquesk

Finding count with two conditions from associated tables.

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))

1 Solution

Accepted Solutions
Marcushenriquesk
Author

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')))

View solution in original post

2 Replies
Kushal_Chawda

@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))

Marcushenriquesk
Author

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')))