Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

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

1 Solution

Accepted Solutions
Highlighted
Contributor

Re: today so far compared with yesterday same time

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
Highlighted
Contributor III

Re: today so far compared with yesterday same time

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

Highlighted
Contributor

Re: today so far compared with yesterday same time

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

 

Highlighted
Contributor

Re: today so far compared with yesterday same time

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

 

Highlighted
Contributor

Re: today so far compared with yesterday same time

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?

 

 

Highlighted
Contributor

Re: today so far compared with yesterday same time

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