topic calculate switched cases in QlikView

calculate switched cases

gabroteddy — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: calculate switched cases

alex00321 — Fri, 28 Feb 2020 04:18:58 GMT

Will this work?

Re: calculate switched cases

gabroteddy — Fri, 28 Feb 2020 07:44:17 GMT

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?

Re: calculate switched cases

alex00321 — Sat, 29 Feb 2020 12:02:26 GMT

Try use this:

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

to see if it works. Thanks!

Re: calculate switched cases

gabroteddy — Sat, 29 Feb 2020 14:35:25 GMT

No, not works....😭

Other ideas?

Re: calculate switched cases

alex00321 — Mon, 02 Mar 2020 01:57:57 GMT

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

Re: calculate switched cases

gabroteddy — Tue, 03 Mar 2020 11:44:44 GMT

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?

Re: calculate switched cases

alex00321 — Wed, 04 Mar 2020 02:15:48 GMT

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