Error in set modifier expression

[khaledsaltaher]

Hi, 

I'm trying to create two things together to read the data correctly from the DB, what I'm trying to do is to create a KPI using this code to: 
1- Convertolocaltime

2- count distinct id's 

Count({<num(converttolocaltime(DateNum,'Eastern Time (US & Canada)'))={">=$(=Num(YearStart(Max(DateNum))))<=$(=(Max(DateNum)))"},Date=,[Month]=,[Year], [YearMonth]=,[Week]=,[Quarter]=, active = {'1'},archived={'0'}>} distinct id)

Anyhelp?

[zar · Accepted solution]

Hi!

This is not how set expression works:

num(converttolocaltime(DateNum,'Eastern Time (US & Canada)'))={">=$(=Num(YearStart(Max(DateNum))))<=$(=(Max(DateNum)))"} 

Please check for more info: https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Se...

Remember,  inside set expression is Field = {value or expression}, not expression = {Expression}.

To solve this problem you should create a Field with ConvertLocal and then use that field as part of your set expression:

Count({<[ConvertedLocal_DateNum]={">=$(=Num(YearStart(Max(DateNum))))<=$(=(Max(DateNum)))"} ,Date=,[Month]=,[Year], [YearMonth]=,[Week]=,[Quarter]=, active = {'1'},archived={'0'}>} distinct id)

Please note use Floor and not Num when convert to Local time, unless you are interested in hours and not dates.

Example script I worked on:

DATA:

Load

Date#(Date,'MM/DD/YYYY')asDate

    ,[active]

    ,[archived]

    ,[id]

;

Load * Inline [

Date ,active ,archived ,id

01/01/2022 ,1 ,0 ,1

12/01/2024 ,1 ,0 ,2

]

;

MasterCalendar: 

Load 

 TempDate AS Date, 

 Floor(converttolocaltime(TempDate,'Eastern Time (US & Canada)')) as ConvertedLocal_DateNum,

 Num(TempDate) as DateNum,

 week(TempDate) As Week, 

 Year(TempDate) As Year, 

 Month(TempDate) As Month, 

 Day(TempDate) As Day, 

 Date(TempDate,'YYYY-MMM') as [YearMonth],

 'Q' & ceil(month(TempDate) / 3) AS Quarter, 

 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

 WeekDay(TempDate) as WeekDay 

;

//=== Generate a temp table of dates === 

LOAD 

 date(mindate + IterNo()) AS TempDate

 ,maxdate // Used in InYearToDate() above, but not kept 

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

 min(FieldValue('Date', recno()))-1 as mindate,

 max(FieldValue('Date', recno())) as maxdate

AUTOGENERATE FieldValueCount('Date');