Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need qtd for month selected, and parallely i need to show previous 3 quarters data on a barchart... itried but it is only showing monthly data... can some one help me..
pleae find attached..
Hi Praveena,
Use this expression in chart
=Count(Distinct {$
< Year_PP = ,
Month_PP = ,
Quarter_PP = ,
MonthN_CP =,
Date_PP={'>=$(=QuarterStart(Max(Date_PP), -3))<=$(=Max(Date_PP))'}
>} [Patient ID])
Hope it helps you.
Regards,
Jagan.
Hi Jagan,
Thanks for the quick reply...
Now, it is showing for QTD, but It is counting patients who entered on same date as one, even though they are distinct persons.. how can I avoid it? Jagan Mohan
Hi,
Try this
=Count({$
< Year_PP = ,
Month_PP = ,
Quarter_PP = ,
MonthN_CP =,
Date_PP={'>=$(=QuarterStart(Max(Date_PP), -3))<=$(=Max(Date_PP))'}
>} [Patient ID])
Removed Distinct, now it counts correctly, if it not works, can you attach some sample file it would be easier to understand.
Regards,
Jagan.
Hi,
I need to show Distinct Patient ID but it is applying distinct to date also... you can find sample file here...
Awaiting for your reply.,
Thanks,
Praveena
Hi Praveena,
I think you need the Distinct Patient count for the last four quarters, but if the same patient exists in both the quarters then it is counted as twice. If you want to count the patients unique then you need to remove the Quarter dimension.
If you want only new patients count then flag the first record of a patient and use this field for calculation.
Try script like this
Table:
LOAD
*
If(Previous(PatientID) <> PatientID, 1, 0) AS NewFlag
FROM DataSource
ORDER BY PatientID, DateDimension;
Now use this as expression in the chart
Sum
( {$
< Year_PP = ,
Month_PP = ,
Quarter_PP = ,
MonthN_CP =,
Date_PP={'>=$(=QuarterStart(Max(Date_PP), -3))<=$(=Max(Date_PP))'}
>} NewFlag)
This calculated the Patient only once.
Hope this is what you expects.
Regards,
Jagan.
Jagan,
Sorry for not being clear to u before;
Yes, I am expecting Distinct Patient count for the last four quarters, but if the same patient exists in both the quarters then it is counted as twice. this is what expected...
Hi,
Since there is a quarter dimension in the chart, it is difficult to calculate the unique patient count. Will give a try.
Regards,
Jagan.
Hi,
I am expecting Distinct Patient count for the last four quarters, but if the same patient exists in both the quarters then it is counted as twice and
eventhough i am showing quarters data on chart, i am having monthly filter;so if any of the month(quarter starting month)) like jan or apr or jul or oct selected, it should give me only that month distinct patientIDs and for other months cumulative distinct patient id's for that QTD needed.
For Example
Patient ID Reported date(MM/DD)
1 01/ 01
2 01/01
3 01/ 02
Patient ID Reported date(MM/DD)
1 02/01
2 02/ 01
4 02/ 02
when i select january, it should show me 3 as count, when i select feb, it should show me 4
the solution given by jagan
Count(distinct {$
< Year_PP = ,
Month_PP = ,
Quarter_PP = ,
MonthN_CP =,
Date_PP={'>=$(=QuarterStart(Max(Date_PP), -3))<=$(=Max(Date_PP))'} is working for count but , if the distinct patient Id's reported on same date, it is counting as 2 for Jan .. (may be distinct is taken to Date also)..
Please help me..
Thanks,
Praveena
If you're using month as dimension and want cumulative counts then set analysis alone won't be enough. You will need one of the solutions described in this document: Calculating rolling n-period totals, averages or other aggregations