Qlik Community

Ask a Question

QlikView Documents

QlikView documentation and resources.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER

QlikView App: Set Analysis - Prior Period Comparison

stevedark
MVP
MVP

QlikView App: Set Analysis - Prior Period Comparison

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

Attachments
Comments
stevedark
MVP
MVP

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

0 Likes
Not applicable

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

0 Likes
stevedark
MVP
MVP

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.

0 Likes
novolouy
Contributor III
Contributor III

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

cyruslentin
Contributor II
Contributor II

Is there a QV-PE enabled format?

0 Likes
stevedark
MVP
MVP

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

0 Likes
cyruslentin
Contributor II
Contributor II

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
MVP
MVP

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

0 Likes
Not applicable

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

0 Likes
stevedark
MVP
MVP

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

0 Likes
Version history
Revision #:
2 of 2
Last update:
‎2019-08-23 10:43 AM
Updated by:
 
Contributors