IF with And Operator

[tomf122]

I am creating an If statement which is connected to a variable and having trouble getting it to run. 

I could get it to run when i had just the Day set to Monday but when i tried to add the shift time i am keep getting 0 as a result. Is there a different way i should be approaching this from.  I want to see the number of MVT which occur's on a Monday and at those shift hours.

if(vdaydropdown = 'Monday' and MATCH(SHIFT_HOUR,'07','08','09','10','11','12','13','14'),
Count({< entry_day = {'Monday'} , SHIFT_HOUR = {'07','08','09','10','11','12','13','14'}>} MVT))

[JHuis]

Dear Tom,

Maybe:

if($(vdaydropdown) = 'Monday' and MATCH(SHIFT_HOUR,'07','08','09','10','11','12','13','14'),
Count({< entry_day = {'Monday'} , SHIFT_HOUR = {'07','08','09','10','11','12','13','14'}>} MVT))

Or else could you give a example of your data?

[tomf122]

I have tried the above but no luck. Getting following error.

The data i have is simple enough.  Just days and shift and then a value for MVT. I just want the count of when a row is populated not the sum. 

[tomf122 · Accepted solution]

I was able to get the following to work but unsure the reason why it wouldnt work with an AND clause it the if statement. 

if(vdaydropdown = 'Monday' ,
Count({< entry_day = {'Monday'} , SHIFT_HOUR = {'07','08','09','10','11','12','13','14'}>} MVT))

[JHuis]

The IF statement is something like if.

Maybe only use the count?

Count({< entry_day = {'Monday'} , SHIFT_HOUR = {'07','08','09','10','11','12','13','14'}>} MVT)

[tomf122]

I have the IF as i need to set it for each day of the week, for this example I only brought out one of the day as otherwise it was too long to read. I have it working now so thanks for your help on the matter. 

[Or]

Your original formula wouldn't work because you have the detailed if() evaluating outside the Count(). The If() statement would be re-evaluated for each line in the table/object, whereas what you should be doing is counting all lines that meet a certain condition.

[tomf122]

Just for clarity on the matter and for future references, I needed to put the count outside the entire IF statement. 

Would the below be a more acceptable solution.

COUNT(if(vdaydropdown = 'Monday' and MATCH(SHIFT_HOUR,'07','08','09','10','11','12','13','14'),
{< entry_day = {'Monday'} , SHIFT_HOUR = {'07','08','09','10','11','12','13','14'}>} MVT), 

if(vdaydropdown = 'Tuesday' and MATCH(SHIFT_HOUR,'07','08','09','10','11','12','13','14'),
{< entry_day = {'Tuesday'} , SHIFT_HOUR = {'07','08','09','10','11','12','13','14'}>} MVT)) 

[Or]

Assuming vdaydropdown is a variable, or a field that only has a single value selected, there's no problem having that part of the if() statement outside the count. However, anything pertaining to the detailed rows (in this case, SHIFT_HOUR) has to be inside of the aggregation function so it is evaluated within the scope of the aggregation. What you wrote actually translates to:

If the only value of vdaydropdown is Monday, and the only value of SHIFT_HOUR matches 07 through 14, then Count(something).

Since there are multiple values of SHIFT_HOUR, this doesn't evaluate to true.

Wouldn't it be easier to have your users make a selection on entry_day rather than using a variable and if/sets? That seems to be what you're doing anyway.

[tomf122]

You make some great points which I didn't think of. I have only started using Qlicksense for the last month or two and have been basically training myself so still a lot of areas I need improvement in. The reason for using two variables is I needed to split the shift hours into a Day/ Evening shift which is why i am using the Match function for shift. I do have a variable set up for the day of the week as this allows them to see the productivity across a certain day.  Below is the chart's i was creating which would show the output each shift has completed.