Cannot subtract days from Week Start Date

[narband2778]

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

[Thiago_Justen_]

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)

[rwunderlich]

Try it without the Date() function.

-Rob

[narband2778]

Hi Rob,

Didn't work

Thanks,

Naresh

[dwforest]

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)

[Thiago_Justen_]

Did you try my suggestion?

[narband2778]

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

[narband2778]

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

[narband2778]

Hello Everyone,

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

Thanks,

Naresh

[dwforest]

=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.