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]

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

[#3]

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]

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 :

MONTH	LICID	STATUS
APR 2018	1	Active
APR 2018	2	Active
APR 2018	3	Inactive
APR 2018	4	Active
MAR 2018	1	Active
MAR 2018	2	Active
MAR 2018	3	Inactive
FEB 2018	1	Terminated
FEB 2018	2	Active
FEB 2018	3	Terminated
JAN 2018	1	Active
JAN 2018	3	Active

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.

[stevedark]

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

[sbiradar]

Thank you Steve.

[killersswang]

@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]

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