Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

quarter to date in a barchart

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..

11 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable
Author

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...

jagan
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable
Author

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand