topic Re: Prior Period Calculation without Date island in QlikView

Prior Period Calculation without Date island

tchovanec — Thu, 24 Apr 2014 13:29:29 GMT

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.

Re: Prior Period Calculation without Date island

whiteline — Thu, 24 Apr 2014 15:01:37 GMT

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

Re: Prior Period Calculation without Date island

tchovanec — Thu, 24 Apr 2014 15:05:40 GMT

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

)))

Re: Prior Period Calculation without Date island

whiteline — Thu, 24 Apr 2014 15:11:15 GMT

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.

Re: Prior Period Calculation without Date island

tchovanec — Thu, 24 Apr 2014 15:14:48 GMT

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

Re: Prior Period Calculation without Date island

whiteline — Thu, 24 Apr 2014 15:19:25 GMT

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

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

Re: Prior Period Calculation without Date island

tchovanec — Thu, 24 Apr 2014 15:24:44 GMT

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.

Re: Prior Period Calculation without Date island

PrashantSangle — Thu, 24 Apr 2014 15:41:56 GMT

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,

Re: Prior Period Calculation without Date island

tchovanec — Thu, 24 Apr 2014 15:53:37 GMT

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

)))