calculate switched cases

[gabroteddy]

I have to intercept (and calculate) the change of drugs distributed (AP_MINSAN10) between two delivery dates (FA_DATA_EROG) per patient (c.d. SWITCHED PATIENTS)

In the example attached the 

PROVA PAZIENTE1 have n.2 switch

PROVA PAZIENTE2 have n.3 switch

PROVA PAZIENTE3 have n.1 switch

Can you give me the correct expression for count these occurences?

[alex00321]

Will this work?

[gabroteddy]

Thank you Alex but not work.

I must consider both information THERAPY (APMINSAN) and delivery dates (FA_DATA_EROG) to calculate siwtches.

the expression that you propose

Count(DISTINCT FA_DATA_EROG)-1

consider only the delivery date.

The expression does not have to calculate the switch if a patient continue the same therapy on two or more delivery dates (FA_DATA_EROG) following one another  

Can you help me?

[alex00321]

Try use this:

count(Aggr(Count(distinct AP_MINSAN10),PAZIENTE,FA_DATA_EROG))-1

to see if it works. Thanks!

[gabroteddy]

No, not works....😭

Other ideas?

[alex00321]

Hi, can you check attached file, seems working for me?

[gabroteddy]

No alex,

the result of your expression is correct but only for the data of the provided excel table, since the data grow and change over time in my database, the result of  your expression does not give me the expected result.

Have you any other idea?

[alex00321]

Hi can you give some data on where you see the unexpected result?