set analysis and dollar sign expansion: different results according to selection
I consulted several documents, discussions and posts, but I didn't find a solution
I'm performing a count of events (reservations for clinical exams) based on periods between different agenda's definitions
in my dataset, I have, among others, the four data involved in this set analysis: the id of the agenda (there are >500 different agendas) the Id the slot, date and time of reservation, date of agenda definition (Cod_Agenda, ID_SLOT, DATA_ORA_PRENOTAZIONE, DATA_GENERAZIONE PERIODO). The amount of data is quite huge (300.000 rows)
Now, for each agenda, i wish to know how many reservations have been done after each agenda definition. My analysis is based on dta of a six months period. Agendas definition is performed in different days for each agenda and there aree too many dates to setit as a dimension. So my dimensions are based on the agenda id (Cod_Agenda) and related issues, suc ad branch.
first of all I got, for each agenda, the latest DATA_GENERAZIONE_PERIODO, the 2nd latest and the3rd latest
Now I wish to answer my question.
To do this i needed to overcome the difference in data format between DATA_GENERAZIONE_PERIODO and DATA_ORA_PRENOTAZIONE. I solved using a variable (CompAggiorPrec), which generates for each value the date and time format
then i used the set analysis. I tried a lot of different ways and or they give 0 value (wrong ftatemts, discarded), or they give me a value that is either correct or wrong depending on the selection.The following is one of them (I found four different but equivalent ways to get this result)
CompAggiorPrec: If (Max (DATA_GENERAZIONE_PERIODO, 2)>0, '">"&Max(DATA_GENERAZIONE_PERIODO, 2)&' 12:00:00"',Null())
$(CompAggiorPrec), which gives the date and time in number/texyt format, e.g 14/06/2018 12:00:00
You cannot use Set Analysis for this case as both DSE and Set expression is evaluated only once per chart and you need to use a different value per Agenda. You will have to use an count(if()...) instead.