Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qliklizzy
Creator II
Creator II

today so far compared with yesterday same time

Hi

I want to calculate yesterdays attendances against today so far.

today so far in my live feed is just count(unitnumber)

but for yesterday I can pull the date through and it counts all of yesterday; but if its 10am I need the count up until then.   I have another field called RunTime - how do I combine them into the below?

if(AdmDate=today()-1 , AdmDate)   = yesterdays date

Thanks

Liz

Labels (4)
1 Solution

Accepted Solutions
qliklizzy
Creator II
Creator II
Author

I've done it!   bit of endless googling before and after posting on here never quite finding the answer but played with it and got the correct output!  - thanks for you help

=count(distinct(if(
AdmDate=today()-1 and AdmissionDateMonth < RunDateTime-1, UnitNumberMonth)))

View solution in original post

5 Replies
asinha1991
Creator III
Creator III

if(AdmDate=today()-1 and Runtime=num(hour(now())) , AdmDate)

makesure runtime has number value from 0 to 24, else adjust accordingly either on right or left

qliklizzy
Creator II
Creator II
Author

hello again,

no it didn't work what do you mean 0-24?

the run date time is 03/10/2019 09:50:00

 

thanks

 

qliklizzy
Creator II
Creator II
Author

hi

I changed it to this

=if(AdmDate=today()-1 and hour(RunDateTime)=num(hour(now())) , AdmDate)

and it return yesterdays total still - is this because its in the dimension, should it go elsewhere

because the hour now does = hour yesterday, so my sum needs to only sum from midnight to 11 but its not.

 

thanks

 

qliklizzy
Creator II
Creator II
Author

Hi

 

I managed to solve it 

=if(AdmDate=today()-1
and AdmissionDateMonth<RunDateTime-1
,
AdmDate)

but this doesn't work In a text object box - do you know why?

 

 

qliklizzy
Creator II
Creator II
Author

I've done it!   bit of endless googling before and after posting on here never quite finding the answer but played with it and got the correct output!  - thanks for you help

=count(distinct(if(
AdmDate=today()-1 and AdmissionDateMonth < RunDateTime-1, UnitNumberMonth)))