Hi all
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)
variables
CompAggiorPrec: If (Max (DATA_GENERAZIONE_PERIODO, 2)>0, '">"&Max(DATA_GENERAZIONE_PERIODO, 2)&' 12:00:00"',Null())
check expression
$(CompAggiorPrec), which gives the date and time in number/texyt format, e.g 14/06/2018 12:00:00
expression
Count( { $ < DATA_PRENOTAZIONE_SLOT= {">$(=$(CompAggiorPrec))"} >} ID_SLOT)
This way, or one equivalent, if do no selection I get a wrong result, if I select the branch, the result is approximate, if I select the agenda, it is right. But agenda are >500.
no selection:
Branch | Cod_Agenda | Slots | Reserv | R in period | Agenda's Changes | Last Change | 2ndLast | 3rdLast | in period > 2nd last |
AG | AG026-1 | 85 | 76 | 55 | 0 | 27/02/18 | - | - | 55 |
AG | AG204-1 | 128 | 108 | 88 | 1 | 24/04/18 | 17/01/18 | - | 88 |
AG | AG216-1 | 397 | 332 | 296 | 3 | 03/08/18 | 14/06/18 | 17/05/18 | 36 |
selection:
Branch | Cod_Agenda | Slots | Reserv | R in period | Agenda's Changes | Last Change | 2ndLast | 3rdLast | in period > 2nd last |
AG | AG026-1 | 85 | 76 | 55 | 0 | 27/02/18 | - | - | 55 |
AG | AG204-1 | 128 | 108 | 88 | 1 | 24/04/18 | 17/01/18 | - | 88 |
AG | AG216-1 | 397 | 332 | 296 | 3 | 03/08/18 | 14/06/18 | 17/05/18 | 162 |
I red hundreds of pages on text and value in dates, data format, variables, dollar sign expansion, set modifiers, and I found no solution.
Many thanks for helping
