4 Replies Latest reply: Apr 23, 2018 12:22 PM by Sunny Talwar

# 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

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

• ###### 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

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

• ###### 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.

data:

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

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

Tenants,

[Total Contractual Income];

[

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.