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

[#2]

Hi Steve,

Thanks for sending me the syntax. I still have a problem. Sorry, I'm still a newbie to Qlik.

When I implemented the sytnax by creating vYear and vThisMonth, I got a value, but it is becoming static and not changing with my selections. and that is why I created variables which has got my MMM-YYYY  format for my minimum date and maximum date in my file.

My original default date is in the format of dd/mm/yyyy and I have changed the date format to MMM-YYYY by using the below formula.

Dual(Date([DEFAULT_DT], 'MMM-YYYY'), MonthEnd(DEFAULT_DT))as new_date

Now what I have done is as below.

vYearStart =Month(AddMonths(Today(),-6))& ' - ' &Min(Year(Date([DEFAULT_DT], 'MMM-YYYY'))) - this will give me start of my year which is Jan-2016

vMTH_Year =Month(AddMonths(Today(),-1)) & ' - ' & Max(Year(Date([DEFAULT_DT], 'MMM-YYYY'))) - this will give the current month and year data which is Jun-2017.

Now my syntax to sum is as below:

= sum({<new_date={">=$(vYearStart)<=$(vMTH_YEAR)"},KEY_RANK={1},BALANCE={"<0"}>} BALANCE) .

The result what I get is 0.

I'm still not getting the right answer.

Can you please help me with the above syntax.

Thanks in advance.

Sowjanya

[#3]

Hi Steve,

Can I have your email ID please?

thanks

Sowjanya

[stevedark]

Hi,

You don't need to explicitly make the date a Dual, this is handled automatically by the date function.  This means you can simplify the load of the new_date field as:

Date(MonthStart([DEFAULT_DT]), 'MMM-YYYY') as new_date,

Similarly, the variables can be done in the same way, using the date function to get the format, rather than building it yourself.

vYearStart

=Date(MakeDate(Year(min([DEFAULT_DT])), Month(AddMonths(Today(), -6))), 'MMM-YYYY')

vMTH

=Date(MakeDate(Year(max([DEFAULT_DT])), Month(AddMonths(Today(), -1))), 'MMM-YYYY')

Again, test that every component part is working in text boxes.  Ensure the variables have the code in them, not the resultant value (in the Variable Settings dialog) or they will not respond to selections. Build the Set Analysis up one statement at a time, so you can see which bit breaks it, i.e.

= sum( BALANCE) .

= sum({<BALANCE={"<0"}>} BALANCE) .

= sum({<KEY_RANK={1},BALANCE={"<0"}>} BALANCE) .

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

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

Hope that helps.

Steve

[#5]

Hi Steve,

Thanks a ton.. It worked finally. The main problem is that my New_Date though it was showing in the "mmm-yyyy" format , actually it isn't. I have used your syntax, then it worked.

Kind Regards

Sowjanya

[stevedark]

Yes, things looking the same and being the same are two different things!

The fact you were using MonthEnd in the expression, rather than month start would not have helped.

Glad it is now working.

Steve

[#7]

Hi Steve,

Can you  please guide me on the similar concept of how to aggregate the sum of distinct count of a column.

My Dates are between jan-2016 to jun-2017.  I need the sum (distinct count( write-offs) by dates selection. Below is the my count syntax. But I need sum( this counts syntax) it is throwing me an error saying we cannot nest aggregation.

  count(distinct{<NEW_MEND_DATE={">=$(vmin_mendate)<=$(vmax_mendate) )"}>} WRITE_OFF)

Thanks

Sowjanya

[#8]

Hi

just amended my code and can see the numbers. but somehow it is not picking my last month counts. My text box is showing the right months for the variables I have created. but when I put it in the formula it is not taking the previous months counts.

for current month it is -14

last month count = 13

but my results is coming as 14 only for the below expression.

= sum(Aggr(count(distinct(WRITE_OFF)), NEW_MEND_DATE={">=$(vmin_mendate)=$(vmax_mendate_PREV))"}>}))
vmax_mendate =Max(Date([NEW_MEND_DATE], 'MMM-YYYY'))

vmin_mendate =Min(Date([NEW_MEND_DATE], 'MMM-YYYY'))

vmax_mendate_PREV = =Date(Max(NEW_MEND_DATE) - 1, 'MMM-YYYY')

Please advise.

thanks

Sowjanya

[stevedark]

Are you missing a < in the set analysis of your sum?

Steve

[#10]

Hi Steve,

I need to calculate the final balance based on Min Year start and Max Year start . the  date format is in the MMM-YYYY form. I'm writing the below syntax. I know somewhere is wrong. can you please guide me in writing the new syntax.

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

Thanks in advance.

Sowjanya

[#11]

Hi Steve

The distinct counts one worked. thanks for helping me out.

thanks

Sowjanya