use Firstsortedvalue result into set analysis

[araneholly]

Hi all,

I have a table where I can calculate in week the day where i have the max value of a measure group by place.

Here below the formula:

FirstSortedValue(DataCreazione, - aggr(max({<DataCreazione={">=$(vDataFrom)<=$(vDataTo)"}, status={"ASSIGNED"},Fonte={"Device"}>}aggr(count({<DataCreazione={">=$(vDataFrom)<=$(vDataTo)"}, status={"ASSIGNED"},Fonte={"Device"}>}tot_tid)/count({<DataCreazione={">=$(vDataFrom)<=$(vDataTo)"}, Fonte={"Device"}>}tot_tid),DESC1,DataCreazione)), DESC1, DataCreazione))

if i try to use this formula into a variable it doesn't work, because for some DESC1 it is null. I tried also a to insert in a formula like this

sum(aggr({< status={"UNASSIGNED"},Fonte={"Device"},
DataCreazione= {"$(=FirstSortedValue(DataCreazione, - aggr(max({<DataCreazione={">=$(vDataFrom)<=$(vDataTo)"}, status={"ASSIGNED"},Fonte={"Device"}>}aggr(count({<DataCreazione={">=$(vDataFrom)<=$(vDataTo)"}, status={"ASSIGNED"},Fonte={"Device"}>}tot_tid)/count({<DataCreazione={">=$(vDataFrom)<=$(vDataTo)"}, Fonte={"Device"}>}tot_tid),DESC1,DataCreazione)), DESC1, DataCreazione)),)"}>}
COUNT({< status={"UNASSIGNED"},Fonte={"Device"},
DataCreazione= {"$(=FirstSortedValue(DataCreazione, - aggr(max({<DataCreazione={">=$(vDataFrom)<=$(vDataTo)"}, status={"ASSIGNED"},Fonte={"Device"}>}aggr(count({<DataCreazione={">=$(vDataFrom)<=$(vDataTo)"}, status={"ASSIGNED"},Fonte={"Device"}>}tot_tid)/count({<DataCreazione={">=$(vDataFrom)<=$(vDataTo)"}, Fonte={"Device"}>}tot_tid),DESC1,DataCreazione)), DESC1, DataCreazione)))"}>}
tot_tid),DESC1))

but it returns 0 to all rows also the ones with the new data valorized.

Do you have any suggestions to make it works?

Kind Regards,

Simone

[stevedark]

Hi @araneholly 

I'm probably oversimplifying this way too much, but if the problem is caused purely when there are null values in DESC1, can you not just take those out with set analysis; DESC1*={*} ?

Something else that might be causing grief is that set analysis gets very picky about dates, so I would always use a Date function to format any output before using it in set analysis.

Your idea of having the FirstSortedValue returned from a variable is sound, as you can then unit test this. Make sure you have an equals sign at the start of the expression (else you will just pass the code into your expression, not the formatted date).

Hope that helps move you in the right direction.

Steve