topic Re: Till Yesterday Count. in QlikView

Till Yesterday Count.

Wed, 25 Nov 2020 16:16:04 GMT

Hi All ,

Can any one help me with YTD logic

I need to take count of ID's till yesterday .

My data is from 1st May , 2016

I need the count of all ID's till yesterday i.e from 1st May to 14-06-2016 .(day before current date) in set analysis.

Re: Till Yesterday Count.

sunny_talwar — Wed, 15 Jun 2016 09:14:38 GMT

May be like this:

Count({<Date = {"$(='<=' & Date(Today() - 1, 'DD-MM-YYYY'))"}>} ID)

Or this for a distinct count:

Count(DISTINCT {<Date = {"$(='<=' & Date(Today() - 1, 'DD-MM-YYYY'))"}>} ID)

Re: Till Yesterday Count.

PrashantSangle — Wed, 15 Jun 2016 09:40:14 GMT

hi,

or may be

count({<DateField={"$(<=Date(Today()-1))"}>}Distinct id)

careful with date format while comparing.

Regards

Re: Till Yesterday Count.

sunny_talwar — Wed, 15 Jun 2016 09:43:50 GMT

You mean this max?

Count({<DateField={"<=$(=Date(Today()-1))"}>}Distinct id)

Re: Till Yesterday Count.

PrashantSangle — Wed, 15 Jun 2016 09:47:38 GMT

yes..

my bad.

thanks for correcting.

Regards

Re: Till Yesterday Count.

Wed, 15 Jun 2016 14:16:20 GMT

Hi Sunny,

I tried your solution but, nothing is coming.

Let me explain you again my requirement :

I have my data from 1st May 2016. I want count of all ID's from 1st May till yesterday i.e 14th June 2016.

Reply ASAP..!!

Thanks,

Ashvita

Re: Till Yesterday Count.

sunny_talwar — Wed, 15 Jun 2016 15:23:55 GMT

What is your date field format? Can you share screenshots or a sample qvw to look at?

Re: Till Yesterday Count.

Thu, 16 Jun 2016 04:24:51 GMT

Hi Sunny,

My Date format is 'MM/DD/YYYY'.

Thanks,

Ashvita.

Re: Till Yesterday Count.

PrashantSangle — Thu, 16 Jun 2016 05:38:24 GMT

Hi,

then modify expression as per date format you have.

Try like

Sunny Expression

May be like this:

Count({<Date = {"$(='<=' & Date(Today() - 1, 'MM/DD/YYYY'))"}>} ID)

Or this for a distinct count:

Count(DISTINCT {<Date = {"$(='<=' & Date(Today() - 1, 'MM/DD/YYYY'))"}>} ID)

or in my expression

Count({<DateField={"<=$(=Date(Today()-1,'MM/DD/YYYY'))"}>}Distinct id)

Regards

Re: Till Yesterday Count.

Thu, 16 Jun 2016 06:16:04 GMT

Hi All,

Thanks Sunny and Max Dreamer for solution.

Its Working..

Warm Regards,

Ashvita.

Re: Till Yesterday Count.

Thu, 16 Jun 2016 07:57:09 GMT

Hi All,

Can any one help me with MTD logic, I need to take count of ID's till yesterday.

I have my data from 1st May 2016. I want count of all ID's from 1st June(current month) till yesterday i.e 15th June 2016.

Date Format is 'MM/DD/YYYY'.

Pls reply urgently.

Thanks in Advance .

Re: Till Yesterday Count.

PrashantSangle — Thu, 16 Jun 2016 09:39:39 GMT

Hi,

try this

Count({<DateField={">=$(=Date(Monthstart(Today()),'MM/DD/YYYY'))<=$(=Date(Today()-1,'MM/DD/YYYY'))"}>}Distinct id)

Regards

Re: Till Yesterday Count.

Fri, 17 Jun 2016 08:33:38 GMT

Hi Max,

Above solution given by you is not working properly.

I've used max(date), still its not working.

This is my MTD logic:

count({$<DateField= {'>=$(=Max((MonthStart(DateField)))) <=$(=Max(DateField))'}>} ID)

Regards,

Ashvita.

Re: Till Yesterday Count.

PrashantSangle — Fri, 17 Jun 2016 08:40:07 GMT

Hi,

max() convert date to num so use date() before max()

modify it like

date(max(date),'MM/DD/YYYY')

Regards

Re: Till Yesterday Count.

Fri, 17 Jun 2016 09:46:58 GMT

Hi,

I've used below logic, but it is not giving correct count of ID's.

count({<DateField= {'>=$(=date(Max((MonthStart(DateField),'MM/DD/YYYY')))) <=$(=date(Max(DateField)-1,'MM/DD/YYYY'))'}>} ID)

plz suggest.

Regards,

Ashvita.

Re: Till Yesterday Count.

sunny_talwar — Fri, 17 Jun 2016 10:12:15 GMT

Try this:

Count({<DateField = {">=$(=Date(MonthStart(Max(DateField)),'MM/DD/YYYY'))<=$(=Date(Max(DateField) - 1, 'MM/DD/YYYY'))"}>} ID)

Re: Till Yesterday Count.

Fri, 17 Jun 2016 10:40:20 GMT

Hi Sunny,

Its working..

Thanks a lot...for helping me out

Regards,

Ashvita.

Re: Till Yesterday Count.

sunny_talwar — Fri, 17 Jun 2016 11:27:22 GMT

No problem. I am glad Max and I were able to help you out.

Best,

Sunny