Set Analysis works only with single selected dimension

[felix_kraemer]

Hello,

I try to calculate a value based on the time when it was inserted into the system.

As long as I select one Stay_Date it returns the correct value.

But if I select more than one Stay_Date it returns zero.

What should I change to make it work?

Thank you,
Felix 

[sidhiq91]

@felix_kraemer Could you please provide some sample data for us to look into it?

[ggijben · Accepted solution]

Hi @felix_kraemer ,

This is not how Set Analysis works unfortunately. Set Analysis is evaluated once for a chart and it will not regard the current dimension value for each row. So it doesn't calculate the "Stay_Date - 70" part for each row, but only once for the whole chart. And since there are multiple "Stay Dates" in the chart, it goes wrong.

Im not sure how your datamodel looks like, but you could create a extra column in the script with the following formula: 

IF( Block_Insert_Date >= Stay_Date - 70 AND Block_Insert_Date <= Stay_Date, 1,0) AS Flag_Rooms_Picked

Then use the following formula as your measure:

SUM( {$< Flag_Rooms_Picked = {1} >}  Rooms_picked)

[felix_kraemer]

Hi ggijben,

Thank you for your reply. 

I now understood the evaluation of set analysis. I will do the calculation at a different stage. Will work as well. 

Best regards,

Felix