topic Re: Cannot subtract days from Week Start Date in App Development

Cannot subtract days from Week Start Date

narband2778 — Wed, 15 Aug 2018 15:18:49 GMT

Hello Everyone,

I am using KPI object in Qlik Sense. [Week Start Date] field is in 'DD/MM/YYYY' format. Qlik Sense Forums‌

i am using below expression which is working fine.

=Count({$ < [Week Start Date] = {"$(=Max([Week Start Date]))"}>}Pat_ID)

but If I subtract 7 days from max week start date, it's not working

=Count({$ < [Week Start Date] = {"$(=Date(Max([Week Start Date])-7))"}>}Pat_ID)

Where am I doing wrong??

Thanks,

Naresh

Re: Cannot subtract days from Week Start Date

Thiago_Justen_ — Wed, 15 Aug 2018 16:44:28 GMT

Naresh,

What about this?

Count({$<[Week Start Date]={"$(=WeekStart(Max([Week Start Date]),-1))"}>}Pat_ID)

It will result in a count of Pat_ID for max date -7. So, let's say today is the max date - you'll have count for 05/08/2018 (last week start date).

But, if you want to an acumulate counting I would suggest this:

Count({$<[Week Start Date]={">=$(=WeekStart(Max([Week Start Date]),-1))"}>}Pat_ID)

Re: Cannot subtract days from Week Start Date

rwunderlich — Wed, 15 Aug 2018 18:01:41 GMT

Try it without the Date() function.

-Rob

Re: Cannot subtract days from Week Start Date

narband2778 — Wed, 15 Aug 2018 21:06:10 GMT

Hi Rob,

Didn't work

Thanks,

Naresh

Re: Cannot subtract days from Week Start Date

dwforest — Wed, 15 Aug 2018 21:26:48 GMT

Is your DateFormatString = "DD/MM/YYYY"?

or try addding explicit date format:

=Count({$ < [Week Start Date] = {"$(=Date(Max([Week Start Date])-7),'DD/MM/YYYY')"}>}Pat_ID)

Re: Cannot subtract days from Week Start Date

Thiago_Justen_ — Wed, 15 Aug 2018 21:30:00 GMT

Did you try my suggestion?

Re: Cannot subtract days from Week Start Date

narband2778 — Wed, 15 Aug 2018 21:31:29 GMT

Yeah it is in 'DD/MM/YYYY' format.

tried this still not good

=Count({$ < [Week Start Date] = {"$(=Date(Max([Week Start Date])-7),'DD/MM/YYYY')"}>}Pat_ID)

Thanks,

Naresh

Re: Cannot subtract days from Week Start Date

narband2778 — Wed, 15 Aug 2018 21:35:32 GMT

Yeah tried mate. I am getting count as 0. Not working.

If, you can try with one date field in KPI object i.e. =max(Date)-7 will return num format of the date. If I use the same in set analysis isn't working.

Thanks,

Naresh

Re: Cannot subtract days from Week Start Date

narband2778 — Wed, 15 Aug 2018 21:37:20 GMT

Hello Everyone,

Can someone provide me examples subtracting days from date field in Qlik sense KPI object.

Thanks,

Naresh

Re: Cannot subtract days from Week Start Date

dwforest — Wed, 15 Aug 2018 21:39:09 GMT

=Count({$ < [Week Start Date] = {"$(Date(=Max([Week Start Date])-7),'DD/MM/YYYY')"}>}Pat_ID) ?

Dates in Set Expressions, I've found to be difficult when you start doing math. Another option is to create a variable to get the date value: LastWeek = Date(Max([Week Start Date])-7),'DD/MM/YYYY')

then Count({$ < [Week Start Date] = {"$(LastWeek)}">}Pat_ID)

it has something to do with the $ expansion and how/when the expression is evaluated.

Re: Cannot subtract days from Week Start Date

Thiago_Justen_ — Wed, 15 Aug 2018 21:39:54 GMT

Are you able to share a mock up with us?

Re: Cannot subtract days from Week Start Date

philip_doyne — Wed, 15 Aug 2018 21:46:53 GMT

HI Naresh

Why note use WEEKSTART(today()) ....

Philip

Re: Cannot subtract days from Week Start Date

narband2778 — Wed, 15 Aug 2018 21:48:23 GMT

If I use the variable created by you in KPI object, it's returning Number Format of the date. Expression is returning '0'.

Still not good.

Thanks,

Naresh

Re: Cannot subtract days from Week Start Date

Thiago_Justen_ — Wed, 15 Aug 2018 21:54:23 GMT

What about this example?

Re: Cannot subtract days from Week Start Date

tamilarasu — Thu, 16 Aug 2018 03:27:48 GMT

Try this,

=Count({$ < [Week Start Date] = {"$(=Date(Max([Week Start Date])-7,'DD/MM/YYYY'))"}>} Pat_ID )

Re: Cannot subtract days from Week Start Date

BalaBhaskar_Qlik — Thu, 16 Aug 2018 06:09:50 GMT

May be this:

=Count({$ < [Week Start Date] = {'$(=Date(Max([Week Start Date])-7))'}>}Pat_ID)