Two fields displayed as line charts over a dimension (Set Analysis Question)

[morenoju]

Hi folks,

I have a master time field created as:

MasterTime:
LOAD interval(recno()/288,'hh:mm') as calendar_time
autogenerate(288);

I now want to display two fields speed_value and speed_value_h over this calendar_time in a line chart. Both fields have a value_time and value_time_h created as hh:mm in 5 minutes intervals. For your reference and in case it helps to troubleshoot the issue:

interval((AutoNumber(RecNo(),'$(vItemId)' & [__FK_valueList])-1)*5/60/24,'hh:mm') as value_time

Interval(Round(Frac(Time("start_date",'hh:mm')),1/1440),'hh:mm') as value_time_h

I've started by creating a table with one column showing the calendar_time dimension and two columns with the following measures:

Avg({<value_time=P(calendar_time)>} speed_value)

Avg({<value_time_h=P(calendar_time)>} speed_value_h)

The values displayed are not the ones I would have expected. Seems like the set analysis might be wrong (looks like t's not been taken into account).

Would be able to point me in the right direction?

Thanks!

[Kushal_Chawda]

@morenoju  may be like below

Avg({<value_time={"=value_time=calendar_time"}>} speed_value)

Avg({<value_time_h={"=value_time_h=calendar_time"}>} speed_value_h)

[morenoju]

Thanks @Kushal_Chawda!
Unfortunately I'm getting all nulls though... Maybe the syntax? I've tried a few combinations without success...

[Kushal_Chawda]

@morenoju  try below

Avg({<calendar_time={"=calendar_time=value_time"}>} speed_value)

Avg({<calendar_time={"=calendar_time=value_time_h"}>} speed_value_h)

[morenoju]

Thanks! But the set analysis is somehow still ignored... Gives me the same result for all timeslots.

[morenoju]

I found something helpful here: https://community.qlik.com/t5/Qlik-Sense-App-Development/Set-analysis-equal-to-another-field/td-p/13...

Even if it's not set analysis:

=Avg( If( text(calendar_time)= text(value_time) ,speed_value))

[morenoju]

Folks, this is working, however I had to introduce one more nested if in one of the expressions and the chart takes several seconds to load...

Do you know a better way to do this calculation?

Avg(If(text(calendar_date)=text(calendar_date_h),if(text(calendar_time)=text(value_time_h),speed_value_h)))

[morenoju · Accepted solution]

In the end, I changed my data model from a snowflake to one with a single facts table. I had to use several JOIN, but at the end, having all the data on a single table with a calendar_date and a calendar_time column, makes it way easier for comparisons on the charts.