Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

praveenavelagap
Contributor III

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

Tags (3)
11 Replies
MVP
MVP

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.

praveenavelagap
Contributor III

Re: quarter to date in a barchart

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

MVP
MVP

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.

praveenavelagap
Contributor III

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

Awaiting for your reply.,

Thanks,

Praveena

MVP
MVP

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:

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.

praveenavelagap
Contributor III

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

MVP
MVP

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.

praveenavelagap
Contributor III

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

Please help me..

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


talk is cheap, supply exceeds demand
Community Browser