11 Replies Latest reply: Oct 21, 2013 5:25 PM by Praveena Velagapudi

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

• ###### Re: quarter to date in a barchart

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.

• ###### Re: quarter to date in a barchart

Hi Jagan,

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

• ###### Re: quarter to date in a barchart

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.

• ###### Re: quarter to date in a barchart

Hi,

I need to show Distinct Patient ID but it is applying distinct to date also... you can find sample file here...

Thanks,

Praveena

• ###### Re: quarter to date in a barchart

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:

*

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.

• ###### Re: quarter to date in a barchart

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

• ###### Re: quarter to date in a barchart

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.

• ###### Re: quarter to date in a barchart

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

Thanks,

Praveena

• ###### Re: quarter to date in a barchart

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

• ###### Re: quarter to date in a barchart

Thanks Wassenaar,

I am using quarters as dimension and months as filter... by the way, your post is so informative.. I will find some time to go through  and impliment it..

• ###### Re: quarter to date in a barchart

hi Jagan,

Strange...I can not able to find out why qtd is not calculated correctly, if you vary mar,jun,sep,dec... qtd from 4th bar on barchart  to previous bar varying... example select dec 2009-Q4 December,it shows cumulative count as 11; but when i move on any next selection which can also display Q4 2009 in one of other 3 bars in bargraph it shows 12 ... can you please help me...(if you can once go through sample file attached in previous post)