How to avoid nested IF?

[morenoju]

Hi folks,

I have a line chart that takes over 20 seconds to load because I have to compare the values of two tables on it. For the x-axis I created a master time dimension (calendar_time) and then in one of the measures I'm doing this nested IF that it is probably ruining the performance:

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

My reasoning has been that to show the average of the speed_value_h, I have to ensure that the date and time in that "_h" table match the date and time of the master calendar and master time. It works, but as said, the performance is terrible.

I'm pretty sure there must be a better way to do this and therefore avoid the performance issue. Any suggestion?

Thanks much!

[marcus_sommer · Accepted solution]

I assume that the biggest bottleneck in regard to your UI performance is your datamodel. Ideally it would be a star-scheme and by really huge datasets one big flat-table provides usually even more performance within the UI.

Nevertheless if you moved the condition from the inside to the outside of the aggregation it should be more performant, means something like this:

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

Probably even faster should be to calculate with the date/time-fields instead of handling them as strings and using the result like a flag - this may look like:

avg(speed_value_h) * pick(sign((date1+time1)-(date2+time2))+2, 0, 1, 0)

 - Marcus

[morenoju]

Hi @marcus_sommer , you were right about the data model. Using several JOIN, I have consolidated the data in one single table with a calendar_date and calendar_time column. Everything is way faster now. Thanks.