Qlik Sense - Set Analysis, calculate delta for two time periods

[Anonymous]

Hi All,

first of all, I would like to thank everyone that has been / is contributing to this community. It seems like a very lively one and good at that too. I have been trying to answer my question for the past 2 hours, but to no avail.

My data looks like this:

Reporting Date	Tenants	Total Contractual Income
31/03/2018	A	500
28/02/2018	A	400
31/01/2018	A	500
31/12/2017	A	400
31/03/2018	B	485
28/02/2018	B	444
31/01/2018	B	421
31/12/2017	B	158

I am trying to set up a pivot table which will include the delta between two periods, ideally the most current (31/03/18 and 28/02/2018 or 31/12/2017, alternatively) across both tenants. I have tried the following:

sum({<[Reporting Date.Date] = {"$(=max([Reporting Date.Date]))"}>} [Total Contractual Income]) - sum({<[Reporting Date.Date] = {"$(=max([Reporting Date.Date],2))"}>} [Total Contractual Income])

The result of this is 0, whereas it should not be 0.

Does anybody have an idea why the above formula is not working?

Thanks,

Kai

[sunny_talwar]

Try this

Sum({<[Reporting Date.Date] = {"$(='>=' & Date(AddMonths(Max([Reporting Date.Date]), -1), 'DD/MM/YYYY') & '<=' & Date(Max([Reporting Date.Date]), 'DD/MM/YYYY'))"}>} [Total Contractual Income])

[felipedl]

Hi Kai,

I'm assuming your date field is not being interpreted as a number, instead, it's a string and the set analysis will not work.

With your data, i did:

data:

Load

Date#([Reporting Date],'DD/MM/YYYY') as [Reporting Date],

Num(Date#([Reporting Date],'DD/MM/YYYY')) as [Reporting Date Num],

Tenants,

[Total Contractual Income];

Load * Inline

[

Reporting Date,Tenants,Total Contractual Income

31/03/2018,A,500

28/02/2018,A,400

31/01/2018,A,500

31/12/2017,A,400

31/03/2018,B,485

28/02/2018,B,444

31/01/2018,B,421

31/12/2017,B,158

]

And with your expression: sum({<[Reporting Date Num] = {"$(=max([Reporting Date]))"}>} [Total Contractual Income]) - sum({<[Reporting Date Num] = {"$(=max([Reporting Date],2))"}>} [Total Contractual Income]) 

I obtained:

Tip: it's much easier to deal with dates in a numeric format instead of the textual one and the example above can be done in Sense, just made it on view because it's the one i have installed.

Felipe.

[Anonymous]

Hi Sunny,

thanks a lot for answering so quickly. This formula works in principle on my data. What it does though is adding the two together.

Suppose my total for 31/03/18 is 985 and mu total for 28/02/2018 is 844. Your formula would yield 1,829 as a result. How do I need to modify it in case I wanted to calculate 985 - 844 = 141?

I have tried adjusting it myself, but cannot make it work.

Many thanks,

Kai

[sunny_talwar]

May be this

Sum({<[Reporting Date.Date] = {"$(='>=' & Date(AddMonths(Max([Reporting Date.Date]), -1), 'DD/MM/YYYY') & '<' & Date(MonthStart(Max([Reporting Date.Date])), 'DD/MM/YYYY'))"}>} [Total Contractual Income])

-

Sum({<[Reporting Date.Date] = {"$(='>=' & Date(MonthStart(Max([Reporting Date.Date])), 'DD/MM/YYYY') & '<=' & Date(Max([Reporting Date.Date]), 'DD/MM/YYYY'))"}>} [Total Contractual Income])

[Qlik_Olli]

Thanks for your help Sunny_talwar.

if you need a printout from the beginning to a point in the middle of a month, then this goes like this:

Sum({<[Reporting Date.Date] = {"$(='>=' & Date(MonthStart(Max([Reporting Date.Date])), 'DD/MM/YYYY') & '<=' & vToday)"}>} [Total Contractual Income])

where vToday should then be created in the script as vToday = Today();.