Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

QlikView App: Set Analysis - Prior Period Comparison

cancel
Showing results for 
Search instead for 
Did you mean: 
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

QlikView App: Set Analysis - Prior Period Comparison

Last Update:

Aug 23, 2019 10:43:02 AM

Updated By:

stevedark

Created date:

May 29, 2013 5:38:11 PM

Attachments

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

Comments
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Your set analysis is not being applied correctly, and it is not possible to nest aggregations (without using the AGGR statement).

You may want:

=sum({<new_date={">=$(vYearStart))<=$(vYearMax)"}>}DBAL)-sum({<new_date={">=$(vYearStart))<=$(vYearMax)"}>}WRITE_OFF)-sum({<new_date={">=$(vYearStart))<=$(vYearMax)"}>}SECURED_RECOVERY)


Or, if (and only if) all fields appear in the same table, i.e. there are no joins, then this is possible:

=sum({<new_date={">=$(vYearStart))<=$(vYearMax)"}>}DBAL - WRITE_OFF - SECURED_RECOVERY)


In fact, if all three fields exist in the same table, you are best off (for performance) to create a new field which is the net of the three fields as a new field name and use this.

If the fields are in different tables (so the first syntax is required) you may chose to create a variable that has the set analysis in it and use this, e.g.:

=sum($(vSADateRange)DBAL)-sum($(vSADateRange)WRITE_OFF)-sum($(vSADateRange)SECURED_RECOVERY)

Hope that helps.

Steve

0 Likes
Not applicable

Hi Steve,

I have tried doing this way and it worked. thanks a lot for your response.

=sum({<new_date={">=$(vYearStart))<=$(vYearMax)"}>}DBAL)-sum({<new_date={">=$(vYearStart))<=$(vYearMax)"}>}WRITE_OFF)-sum({<new_date={">=$(vYearStart))<=$(vYearMax)"}>}SECURED_RECOVERY)

thanks

Sowjanya

sbiradar
Contributor
Contributor

Hi Steve,

I have gone through your answers to all queries related to set analysis and felt very helpful and informative.

I have a requirement to make a month-month comparison and generate reports on changes at entity level which caused variations in the KPI's shown on the Qlikview dashboard.

Below is my sample dataset, in this dataset :

   

MONTHLICIDSTATUS
APR 20181Active
APR 20182Active
APR 20183Inactive
APR 20184Active
MAR 20181Active
MAR 20182Active
MAR 20183Inactive
FEB 20181Terminated
FEB 20182Active
FEB 20183Terminated
JAN 20181Active
JAN 20183Active

I want to identify the cumulative changes of the LICID month to month .

when user selects Month1 - JAN 2018 and Month 2 - April -2018

output chart(report) should include LICID 2, 3 and 4 and LICIDs are compared between JAN 2018 & FEB-2018, FEB-2018 & MARCH-2018, MARCH-2018 & APRIL-2018.

we are able achieve the required outcome through scripting, but struggling to achieve this outcome in presentation layer via setanalysis.

can you pls. help here.

                                                                     

0 Likes
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

It is likely that scripting is the best way.  This way you are putting the processing burden on the load script, and making the calculations at run time fewer.

When you have a table with transitions it is often helpful to create another table in a loop which has the counts.

For example, for a row that is added in Jan 18 and removed in Mar 18 you could have:

Code,Month,Count,Movement

ABC,Jan 18,1,1

ABC,Feb 18,1,0

ABC,Mar 18,0,-1

ABC,Apr 18,0,0

Once you have created this analysis is easy.

Cheers,
Steve

0 Likes
sbiradar
Contributor
Contributor

Thank you Steve.

killersswang
Contributor II
Contributor II

@Steve Dark

In the attached qvf, the comparison is made by using gauge chart,  how could we realize this by using bar chart. I have one case,  in the X-axis,  I want to place the latest 6 months  (for example: July to Dec, which have been realized by  dimension limitation ), in the Y=axis,   I want to have 2 column bars  one showing the KPIs of latest 6 month(July-Dec), and the other showing the previous 6 months(Jan-Jun), I try to use " above "  function, but which returns wrong result. since above is calculated on chart level . how could this be realized?

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @killersswang 

I'm not sure if I quite follow what you are after. Is it that one colour of bar should be a rolling six months, which is up to and including the month shown on the X axis? The other colour bar should then be the previous rolling six months?

If so, you should take a look at this blog post and app:
https://www.quickintelligence.co.uk/qlikview-accumulate-values/

This describes how you can create a new Month dimension which rolls up the previous five months into a single value.

In addition to what is described in that post you would also want to add a new column to the second dimension table, which defines whether the month is in the Latest Six or Prior Six months. This would then be used in Set Analysis.

If I've missed what you are looking to achieve please let me know.

Hope that helps.

Steve

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