topic Re: Comparision vs. Previous Period in QlikView

Comparision vs. Previous Period

Anonymous — Wed, 20 Feb 2013 11:51:28 GMT

Good morning,

There's an issue in my Qv document when I compare Current Period Sales vs. Previous Period Sales in some corner cases where my solution (simple and straightforward) doesn't work.

Current Period may be selected by the user freely and may be non-contiguous dates, YTD, and even contiguous dates across two consecutive years mainly for

moving annual total (MAT). Loaded data in the complete model goes from 2009 to 2012.

My solution involves creating a calendar (granularity up to months, not days) where I add a dimension called 'Previous Year&Month' whose value is exactly 12 months before current date. This way I tie current date to 12-months-before date.

And when I want to get sum(Sales) from previous period (not always a 12 months period nor even same months from previous year) the expresion is quite simple: sum({$<CURRENT_YEAR_MONTH=P(PREVIOUS_YEAR_MONTH),CURRENT_YEAR=,CURRENT_MONTH=>}Sales)

It works perfectly if data from ALL current period exist ut if we have "holes" in some months where some data exist from previous period, then data from previous period don't get included in sum() and results from previous period is wrong.

I've tried different approaches to solve this issue: From using 'getfieldselection' in p() (not very elegant and forces to use 'replace' in my expressions catalog in the script to avoid $-expansion annoyances) to 'selectively unselect' dimensions in set analysis '<...DIMENSION=,...>' but it may be quite a mess if there's too many dimensions.

One of my constraints is that I want the user use only year selector and month selector to select months & year (TAM is indirectely selected through button --> then correct year&month data range is automatically calculated, so user doesn't have direct access to year&month concatenated dimension)

Any idea?

I've attached a simple example which illustrates the problem

Thanks in advance,

Raúl

Re: Comparision vs. Previous Period

Gysbert_Wassenaar — Wed, 20 Feb 2013 12:05:27 GMT

See attached qvw

Re: Comparision vs. Previous Period

Wed, 20 Feb 2013 12:35:08 GMT

Hi Raúl,

I have think on two possible solutions. One based on Set Analysis and other with an isolated table for month selections. Attached my example.

Juan Gerardo

Re: Comparision vs. Previous Period

Anonymous — Wed, 20 Feb 2013 12:49:32 GMT

Hi Juan Gerardo,

Thanks for your quick response!

I've checked your solution and it works Ok in the example using one of the approaches I pointed out in my original post.

Problem here is that if you have many dimension which may be selected by the user, it may be a mess excluding them all from all the set analysis expressions in the document.

Could it exist another solution (maybe using data island) which doesn't involve cluttered set analysis expressions?

Regards,

Raúl

Re: Comparision vs. Previous Period

Anonymous — Wed, 20 Feb 2013 12:56:52 GMT

Hi Gysbert,

Sorry, but I think your example is not working properly:

When using 'Alice' & 'Shirts' filter and year is set to 2012, previous sales are 2.266,00€. But when I select 2011, current sales are 3.170,00€ (correct value)

Thanks anyway for your answer!

Regards,

Raúl

Re: Comparision vs. Previous Period

Gysbert_Wassenaar — Wed, 20 Feb 2013 13:02:44 GMT

I thought you wanted to compare the same months. Apparently you do not. So try =Sum({$<YEAR={$(=max(YEAR)-1)}>}SALES)

Re: Comparision vs. Previous Period

Anonymous — Wed, 20 Feb 2013 13:39:54 GMT

Hi Gysbert,

Actually, what I want is keep the time period I originally selected (even if some of the months are excluded by filtering of other dimensions) and shift it 12 months backwards to calculate sum(Sales).

I've tried your solution and it works as long as the months I select are from the same year. If I try to select a range of months from -let's say- nov-11 to july-12 using YEAR_MONTH dimension, it fails.

Thanks again,

Raúl

Re: Comparision vs. Previous Period

Anonymous — Fri, 23 Oct 2015 08:13:26 GMT

Hi,

I just wanted to point out an elegant solution to my problem that I found while surfing the Internet:

QlikFit: Set analysis that ignores all selections except a few

Regards,

Raúl