QlikView App: Set Analysis - Prior Period Comparison

[stevedark]

This QlikView shows how basic Set Analysis syntax can be used to deliver a Prior Period Comparison.

Rather than taking the approach of flagging rows as being in certain periods at load time this example shows the comparisons based on selections.  It is an approach I have used many times on client site and tends to offer the users what they require.

The QlikView was originally written as a response to the following QlikCommunity thread: http://community.qlik.com/message/102230

If you want to copy and paste the table from this example into another document you can set all the variables by adding this code to your load script:

let vMaxDate  = '=max(Date)';

let vMaxDay   = '=day(max(Date))';

let vMaxMonth  = '=month(max(Date))';

let vMaxYear  = '=max(Year)';

let vPriorMonth  = '=month(addmonths(max(Date),-1))';

let vPriorMonthYear = '=Year(addmonths(max(Date),-1))';

let vPriorYear  = '=vMaxYear-1';

let vPriorYearDate = '=date(addyears(max(Date),-1),' & chr(39) & 'DD MMM YYYY' & chr(39) & ')';

Obviously you will need to change the field names to match the date fields in your data model.

There is a Qlik Sense version of this application available for download here:

Qlik Sense App: Prior Period Comparison with Set Analysis

There are a number of other tutorials and downloadable examples on our website here:

https://www.quickintelligence.co.uk/qlikview-examples/

If you have any questions please post them in the comments below.

Steve Dark

www.quickintelligence.co.uk

[stevedark]

The reason that you only see the values for those specific periods is the set analysis in the expression is limiting the results to only those periods.  Adding a date dimension in this way is a good checksum that the set analysis is doing what it should be.

If you simply want values by month then you probably just want a simpler expression - with no set analysis, e.g. sum(Value).

Hope that helps.

Steve

[#3]

Steve - what i am trying to accomplish is a table, by month, which shows the total # for the month, total # from previous month, and % change between the two. I don't think I can use the before/after functions because my source data is thousands of rows...does that make sense? When I try to show this in a table or chart it does not seem to work.

Adam

[stevedark]

That is exactly what this document does.  If you amend the variables and expressions to match your own data model it should work.  The example is only over a handful of rows as it is just demonstrating the principle.  This will work over thousands, or even millions, of rows.

Getting your set analysis right means that you don't need to use the before and after functions.

[novolouy]

Very useful,  thank you for taking the time to create this document and example!

[Anonymous]

Is there a QV-PE enabled format?

[stevedark]

Hi Cyrus, unfortunately I am not able to make the document PE enabled.

I can provide a Sense QVF, which you can load into the free Qlik Sense Desktop and then copy the expressions across from?  Would that be useful to you?

Steve

[Anonymous]

Hey Steve,

Thanks much for your positive reply.

Though I i have used QlikSenseDesktop was meaning to give it a shot. Now I have an added incentive.

Yes, the QVF will do very well specially if it contains the require expression.

Thanks once again.

Regards -- Cyrus

[stevedark]

Hi Cyrus,

You can find the Qlik Sense version of this app at:

Qlik Sense App: Prior Period Comparison with Set Analsysis

Good luck with downloading, installing and getting to grips with Qlik Sense.  It can feel a bit strange at first if you are used to QlikView, but once you get the way of it you will find it is a great product.

Steve

[#10]

Hi Steve,

I have a excel where we upload monthly data to the existing file with the default dates on the file. The file contains default dates and balance amounts based on a relationship id. I want to calculate the sum of balances every month and also see how much has been increased or decreased from last month updated file. I have 2 years worth of data starting Jan-2016 to Current month(June-2017) data.

Example: I need to calculate all the sum of sales starting Jan-2016 to Jun-2017 sum of sales and also include a conditional statement where BAlance <0 and Key_rank = 1 then do sum(Balance) for all the rolling months starting Jan-2016 to Jun-2017.

and also with the same conditional statement I need to find the difference between the sum of sales between Jan-2016 to Jun-2017  and Jan-2016 to May-2017.. this has to continue every month. i.e. sum of all sales year to date and month to date and difference by -1 month.

=if((sum({<[DEFAULT_DT ])] = {">=  $(=Min([DEFAULT_DT ])) < $(=Max([DEFAULT_DT ]]))"}>} and SUM(BALANCE)<0),'(' & '£'& Num(SUM(if(KEY_RANK = 1, BALANCE,0))*(-1)/1000000000 ,'#,##0.00'),
'£'& Num(SUM(if(KEY_RANK = 1, BALANCE,0))/1000000000,'#,##0.00')) & 'bn' & ')' 

thanks in advance

Sowjanya

[stevedark]

Hi,

You will want to create variables for each of the date periods that you have as bounds (vYearStart, vThisMonth and vPriorMonth).  Ensure (using a text box) that these give the values you want - including that the date format matches that of your source data.

Your query blends IF logic and Set Analysis, you will be better off if you can do it all with set analysis.

You will end up with something like:

sum({<DEFAULT_DT={">=$(vYearStart)<=$(vThisMonth)"},KEY_RANK={1},BALANCE={"<0"}>}BALANCE)

Then the same for prior month, and then one over the other.

If you need to pre-aggregate the BALANCE before checking it for zero you may need to use the Aggr function, with an IF statement.  This depends on the granularity of your source data.

Regarding the formatting, and currency suffix, this is usually better done in the Number tab of the chart properties rather than in the expression.

Hope that helps a bit and points you in the right direction.

Steve