Derived fields not working in Set Analysis

[stephencredmond]

Hi,

I have been playing with Declare and Derive.  I have created a calendar using the script from the help however, when I use the field in Set Analysis, I get no limitation.  So the expression:

Sum({<[OrderDate.Calendar.Year]={2013}>}[#Sales Total])

Will calculate across all available values, as if the set was not there.

In the same model, I have also created a Year field from the same OrderDate field - in the "old fashioned" way.  The expression:

Sum({<[Year]={2013}>}[#Sales Total])

Correctly calculates for only values in 2013.

If I juxtapose both year fields on the layout, selections in one field are correctly reflected in the other field.

Are derived fields not supported for Set Analysis?

Regards,

Stephen

[ccaporaso]

Hi Henric,

I've tried to use a year derived field in a simple set analysis expression with total clause and it didn't work.

The field is defined in this way:

Dual(Year($1), YearStart($1))  as [Year] Tagged ('$axis', '$year')

The set analysis expression is this one:

sum({<Divisa_Comprobante={USD}>} Total_Cobrado_Neto_Multimoneda)
/
sum({<Divisa_Comprobante={USD}>} total <[Fecha_Emision_Comprobante.Vision360Calendar.Year]> Neto_Comprobante_Variedad)

Fecha_Emision_Comprobante.Vision360Calendar.Year is one dimension of a pivot table visualization.

If I use a field that belongs to the fact table (year(Fecha_Emision_Comprobante) as Año_Emision_Comprobante) the result is OK.

Is this the expected behaviour, a limitation, a bug?

Any help would be appreciated.

Thanks and kind regards.

Cecilia.

[paolo_mapelli]

Hi Cecilia, it seems very similar to a (possible) bug I've already posted about: https://community.qlik.com/t5/Qlik-Sense-App-Development/possible-BUG-quot-quot-syntax-with-derived-...

[ccaporaso]

Hi Paolo!

Yes, you are right, it seems that derived fields have some bug, limitations or misbehaviour when used in measures. I was surprised that no one except you has mentioned the topic along these months. 

We are in touch in case we find an answer!

Regards,

Cecilia.

[katierusso]

I have also found that derived calendar measures work well the front end charts, but not in set analysis within measures.  Using Qlik sense May 2023 Patch 8. My workaround has been to create a calendar table where I load the calendar fields from the date in my resident table (formatted as date).

let vmaxDate = Peek('DateFromResTable', -1, 'ResTable');

DateFlags:
Load DateFromResTable,
  12*Year('$(vmaxDate)')+Month('$(vmaxDate)')-12*Year(DateFromResTable)-Month(DateFromResTable) AS [MyMosAgo] ,
  If (DayNumberOfYear(DateFromResTable) <= DayNumberOfYear('$(vmaxDate)'), 1, 0) AS [InYTD] ,
  Year('$(vmaxDate)')-Year(DateFromResTable) AS [YearsAgo] ,
  If (DayNumberOfQuarter(DateFromResTable) <= DayNumberOfQuarter('$(vmaxDate)'),1,0) AS [InQTD] ,
  4*Year('$(vmaxDate)')+Ceil(Month(DateFromResTable)/3)-4*Year(DateFromResTable)-Ceil(Month(DateFromResTable)/3) AS [QuartersAgo] ,
  Ceil(Month('$(vmaxDate)')/3)-Ceil(Month(DateFromResTable)/3) AS [QuarterRelNo] ,
  If(Day(DateFromResTable)<=Day('$(vmaxDate)'),1,0) AS [InMTD] ,
  12*Year('$(vmaxDate)')+Month('$(vmaxDate)')-12*Year(DateFromResTable)-Month(DateFromResTable) AS [MonthsAgo] ,
floor((12*Year('$(vmaxDate)')+Month('$(vmaxDate)')-12*Year(DateFromResTable)-Month(DateFromResTable))/12) AS [Rolling12Mo] ,
  Month('$(vmaxDate)')-Month(DateFromResTable) AS [MonthRelNo] ,
  If(WeekDay(DateFromResTable)<=WeekDay('$(vmaxDate)'),1,0) AS [InWTD] ,
  (WeekStart('$(vmaxDate)')-WeekStart(DateFromResTable))/7 AS [WeeksAgo] ,
  Week('$(vmaxDate)')-Week(DateFromResTable) AS [WeekRelNo]
resident ResTable;

[JohnJohn]

Hi,

I find the similar issue, when I use DataDate to create derived fields in script, I could not use them in sum function;

However, when I use date#('$(DataDate)'), all is okay.

I'm not sure if it could solve your problem.