Error in set modifier expression

khaledsaltaher — Mon, 16 Jan 2023 09:57:38 GMT

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?

Re: Error in set modifier expression

zar — Sun, 22 Jan 2023 12:36:28 GMT

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/SetAnalysis/set-analysis-expressions.htm#

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

topic Error in set modifier expression in App Development

Error in set modifier expression

Re: Error in set modifier expression