topic Re: today so far compared with yesterday same time in QlikView

today so far compared with yesterday same time

qliklizzy — Sat, 16 Nov 2024 19:57:58 GMT

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

Re: today so far compared with yesterday same time

asinha1991 — Thu, 03 Oct 2019 09:47:06 GMT

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

Re: today so far compared with yesterday same time

qliklizzy — Thu, 03 Oct 2019 10:33:17 GMT

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

Re: today so far compared with yesterday same time

qliklizzy — Thu, 03 Oct 2019 10:44:42 GMT

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

Re: today so far compared with yesterday same time

qliklizzy — Fri, 04 Oct 2019 10:54:46 GMT

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?

Re: today so far compared with yesterday same time

qliklizzy — Fri, 04 Oct 2019 11:10:12 GMT

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