topic How compare two quarter in different years? in QlikView

How compare two quarter in different years?

Thu, 05 Jan 2012 13:22:07 GMT

I want to know how to compare two quarter in different years in a Chart. For example If I select Q2 and select year 2010, I want to compared it with the previews year Q2 of 2009, I have try but no luck I will appreciate your

help Thanks

This is how I read the calendar (Example)

MasterCalendar:

LOAD TempDate AS DATE,

Year(TempDate) AS Year,

Month(TempDate) AS Month,

Day(TempDate) AS Day,

Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,

Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

How compare two quarter in different years?

swuehl — Thu, 05 Jan 2012 14:06:12 GMT

For current selection, you do something like

=sum(Value)

Then try

=sum({<DATE= {">=$(=quarterstart(min(DATE),-4))<$(=quarterstart(min(DATE),-3))"}, Quarter=, Year=, Month=, Day, WeekDay=, WeekYear= >} Value)

to retrieve the sum of Values for the same Quarter one year back (complete Quarter, not quarter to date).

How compare two quarter in different years?

Thu, 05 Jan 2012 14:33:30 GMT

Thanks for your help its works

if you can explain the expression I will appreciate

Thanks,

How compare two quarter in different years?

swuehl — Thu, 05 Jan 2012 15:04:03 GMT

This part of the expression

{<DATE= {">=$(=quarterstart(min(DATE),-4))<$(=quarterstart(min(DATE),-3))"}, Quarter=, Year=, Month=, Day, WeekDay=, WeekYear= >}

is a set expression. If you are not familiar with set analysis, please have a look at the Help - set analysis, and for point in time analysis like that I found this blog quite useful:

http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

What my above set expression basically does is setting a selection only in the context of the aggregation expression (i.e. sum( Value) ). I clear all selections in the calendar fields using just the field name and equal sign, like Quarter= , and for field DATE, I set a new list of values that I determine by use of a search expression:

">=$(=quarterstart(min(DATE),-4))<$(=quarterstart(min(DATE),-3))"

Here, the dollar sign expansion (another important piece, please look also for dollar sign expansion into the Help) will be evaluated first:

For example, if you select todays quarter Q1 and year 2012, min(DATE) will be 2012-01-01 or its numerical representation. Quarterstart with second parameter -4 resp. -3 will return the start of quarter 4 quarters back resp. 3 quarters back. So the search looks like this after dollar sign expansion (depending on your date format settings):

">=2011-01-01<2011-04-01"

So we look for all DATEs that are larger equal Jan 1st 2011 and smaller than April 1st 2011, which should be ok for this quarter one year back.

Hope this helps,

Stefan

How compare two quarter in different years?

Thu, 05 Jan 2012 15:12:42 GMT

Thanks for the explanation .

Re: How compare two quarter in different years?

Mon, 22 Aug 2016 13:15:06 GMT

Hi,

I am working with qlik sense and I have an issue to compare my actual quarter with the previous quarter.

for exemple

how can I calculate the difference for each quarter with the previous and if the value is bigger, background color it in red as in the exemple.

In the exemple I used static value to compare like : if(value <20, red()).

Thanks in advance for the replays.