Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
raul_gonzalez
New Contributor II

Comparision vs. Previous Period

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

1 Solution

Accepted Solutions
raul_gonzalez
New Contributor II

Re: Comparision vs. Previous Period

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

7 Replies
MVP & Luminary
MVP & Luminary

Re: Comparision vs. Previous Period

See attached qvw


talk is cheap, supply exceeds demand
Not applicable

Re: Comparision vs. Previous Period

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

raul_gonzalez
New Contributor II

Re: Comparision vs. Previous Period

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

raul_gonzalez
New Contributor II

Re: Comparision vs. Previous Period

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

MVP & Luminary
MVP & Luminary

Re: Comparision vs. Previous Period

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


talk is cheap, supply exceeds demand
raul_gonzalez
New Contributor II

Re: Comparision vs. Previous Period

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

raul_gonzalez
New Contributor II

Re: Comparision vs. Previous Period

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