Prior Period Calculation without Date island

[tchovanec]

I am trying to get the prior year's data without having a date Island. My calendar is associated to my data. For example, if I choose Mar-2014, I want to see that counts for Mar-2013. Here is the set analysis I am using and it shows the correct dates, but it is not returning the right numbers. It is returning the data for the current selection(Mar-2014).

COUNT(

        {<

        ADMIT_DATE = {">=$(=Monthstart(AddMonths(MIN(Date),-12))) <= $(=Monthend(AddMonths(MAX(Date),-12)))"}      

        >}

DISTINCT IF(FLG_SPECIALTY_CLINIC = 1 OR FLG_DENTAL = 1 OR FLG_ORTHODONTICS = 1,Key,

IF(FLG_PRIMARY_CARE = 1 OR FLG_ED = 1 OR FLG_UC = 1, HAR_ID

)))

Any assistance would be really appreciated. Thank you.

[whiteline]

Hi.

I guess the selection is made in Date field. As you pick the current selections set as the basis for set expression user selection is also applied.

You have to clear the user selection .. {<Date=, ADMIT_DATE=...

[tchovanec]

Thanks for your response. I updated the expression to the following, but it still just shows the count for the current dates selected and not the prior period.

COUNT(

        {<

        ADMIT_DATE = {">=$(=Monthstart(AddMonths(MIN(Date),-12))) <= $(=Monthend(AddMonths(MAX(Date),-12)))"}   

        ,Year=

        ,Month=

        ,Date=

        ,ADMIT_DATE=

        >}

DISTINCT IF(FLG_SPECIALTY_CLINIC = 1 OR FLG_DENTAL = 1 OR FLG_ORTHODONTICS = 1,Key,

IF(FLG_PRIMARY_CARE = 1 OR FLG_ED = 1 OR FLG_UC = 1, HAR_ID

)))

[whiteline]

There is no reason for the second ADMIT_DATE=.

It seems that you use if() in your expression with different fields, while the set expression is applied only to count function.

[tchovanec]

If I remove the ADMIT_DATE= it is still not right.

[whiteline]

And I've already said why it could be so.

Start with simple count({<...>} distinct HAR_ID)

[tchovanec]

It has been updated to the following.

COUNT(

        {<

        ADMIT_DATE = {">=$(=Monthstart(AddMonths(MIN(ADMIT_DATE),-12))) <= $(=Monthend(AddMonths(MAX(ADMIT_DATE),-12)))"}   

        ,Year=

        ,Month=

        ,Date=

        ,MonthYear=

        >}

DISTINCT HAR_ID

)

It still is not giving the correct numbers for the prior year's period. I appreciate the help.

[PrashantSangle]

Hi,

I would like to know what is your ADMIT_DATE format.

Is it in Apr 2014 format.

If it is in Apr 2014 format then'

change your set analysis to,

COUNT(

        {<

        ADMIT_DATE = {"=$(=MonthName(AddMonths(MIN(ADMIT_DATE),-12)))"}  

        ,Year=

        ,Month=

        ,Date=

        ,MonthYear=

        >}

DISTINCT HAR_ID

)

If it is in complete date like 01-04-2014 then use

Count({<ADMIT_DATE={">=$(=Monthstart(AddMonths(MAX(ADMIT_DATE),-12)) <=>=$(=Monthend(AddMonths(MAX(ADMIT_DATE),-12))"}>} DISTINCT HAR_ID)

As Looking at your expression ,

There is is small mistake

in your expression

=Monthstart(AddMonths(MIN(ADMIT_DATE),-12))

=Monthend(AddMonths(MAX(ADMIT_DATE),-12))

which is not correct

Use either min() or max().

I think this could be issue for you.

Regards,

[tchovanec]

Ok, Thanks everyone. I found out my issue. It was a space between the $ sign and equal sign.

COUNT(

        {<

       ADMIT_DATE = {">=$(=Monthstart(AddMonths(MIN(Date),-12))) <= $(=Monthend(AddMonths(MAX(Date),-12)))"}   

        ,Year=

        ,Month=

        ,MonthYear=

        ,Date=

        >}

DISTINCT IF(FLG_SPECIALTY_CLINIC = 1 OR FLG_DENTAL = 1 OR FLG_ORTHODONTICS = 1,Key,

IF(FLG_PRIMARY_CARE = 1 OR FLG_ED = 1 OR FLG_UC = 1, HAR_ID

)))