topic Re: Comparing 2 yrs of data in QlikView

Comparing 2 yrs of data

chinnu123 — Wed, 28 Oct 2015 20:17:36 GMT

Hi Community,

I need a help of syntax which i am facing problem displaying current month and previous month

My issue is My current month is October then i need to get

Current Year-from Nov 2014 to Oct 2015

Previous Year-From Nov 2013 to Oct 2013

Can any one please help me

Re: Comparing 2 yrs of data

chancekbarkley — Wed, 28 Oct 2015 20:24:14 GMT

Chinnu,

Are you using a master calendar in your application? If so, you might try adding a flag for rolling 12 months and the rolling 12 prior to that.

If( DateField > addmonths(Today(),-12) and DateField <= Today(),1) AS _R12,

If( DateField > addmonths(Today(),-24) and DateField <= addmonths(Today(),-12),1) AS _R13_24

You can then use set analysis in your expressions SUM({<_R12={1}>}Sales).

Let me know if you need more details.

-cb

Re: Comparing 2 yrs of data

chinnu123 — Wed, 28 Oct 2015 20:35:26 GMT

Hi Barkley,

We are not using Master calender

Re: Comparing 2 yrs of data

chancekbarkley — Wed, 28 Oct 2015 20:42:45 GMT

I cleaned the logic up a bit - just replace "DateField" in the script below with date in your model and put it in your load script.

If( DateField > addmonths(Today(),-12) and DateField <= Today(),1) AS _R12,

If( DateField > addmonths(Today(),-24) and DateField <= addmonths(Today(),-12),1) as _R13_24

If that doesn't work, post the app(if you can) and I'll have a look.

Re: Comparing 2 yrs of data

ramoncova06 — Wed, 28 Oct 2015 20:48:18 GMT

for some reason I am not able to see what Barkley wrote, but you should use a master calendar and then use set analysis to compare

QlikView App: Dates, Date Ranges and Set Analysis

The Master Calendar

Re: Comparing 2 yrs of data

Wed, 28 Oct 2015 20:53:24 GMT

Same with me Ramon.

Chinnu, for previous year try this expression.

=AddMonths(monthstart(today()),-11) gives you November 1st of 2014 & change same to -23 which will give you from October 1st 2013. use that date in if clause and use it in calc. dimension to get your desired result.

But as Ramon told, use master calendar if you dont want to jump thru all these hoops. thats the best approach to solve this issue.

Thanks

Brad.

Re: Comparing 2 yrs of data

chinnu123 — Wed, 28 Oct 2015 21:31:49 GMT

Hi bharadwaaj,

I am not using master calender

I am using below expressions can you tell me any corrections on below expressions

For current month-

Count({$<Month={'>=$(=DATE(AddMonths(Today() , - 11),'MM-YYYY' ))'}>}Incidents)

For previous month

=Count({$<Month={$(=max(Month-22)}>}Incidents)

Thanks in Advance

Re: Comparing 2 yrs of data

chinnu123 — Wed, 28 Oct 2015 22:07:38 GMT

Hi Barkley,

I found one of the expression from community member SWEUHL and got the solution. i am also posting the expression which will be helpful for someone who may get this kinda issue.

=count({<[Employee ID] = p({<[Course Name] = {Course1}>}) * p({<[Course Name] = {Course2}>}) >} distinct [Employee ID])

Regards,

Chinnu

Re: Comparing 2 yrs of data

chinnu123 — Wed, 28 Oct 2015 22:08:24 GMT

Thanks Barkley for your precious time