Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kainehen
New Contributor

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

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 DateTenantsTotal Contractual Income
31/03/2018A500
28/02/2018A400
31/01/2018A500
31/12/2017A400
31/03/2018B485
28/02/2018B444
31/01/2018B421
31/12/2017B158

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

4 Replies
MVP
MVP

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

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
Valued Contributor III

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

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:

sample.png

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.

kainehen
New Contributor

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

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

MVP
MVP

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

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