Skip to main content
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Tables: Collect data from previous dates in a single record

A requirement for the application I'm working on right now is to have the same measure be portrayed with the current month value, the previous month value, and the month prior to that value in a single record for each record in the table. I'm using the following expressions to attempt this now:

Current Month:


Month - 1:

Only({<[Record Date] = {'=$(vMonthPrv1)'}>} [Field])

Month - 2:

Only({<[Record Date] = {'=$(vMonthPrv2)'}>} [Field])

The variables are:


Max([Record Date]) - 1


Max([Record Date]) - 2

The values being returned are all the same values as the Current Month expression. This isn't accurate.

Could anyone point out where I'm going wrong here, or if there's a better way to accomplish this?

Edit: same effect when using Sum() instead of Only().

3 Replies

If the field Record Date contains dates then it's very well possible that three consecutive dates fall in the same month, assuming [Field] is your month field.

Regardless, what you're trying to do won't work since the sets of set analysis expressions are calculated at the chart level, not a the row level. You can try using the Above function or expand your data model with a new field that links each month with its previous months. That's sometimes called an As-Of table

talk is cheap, supply exceeds demand
Contributor III
Contributor III

what you're trying to do won't work since the sets of set analysis expressions are calculated at the chart level, not a the row level

Could you elaborate on this?

The Record Date field is the result of a MakeDate([RecordYYYYMM]) from the data source, so they will all be the first day of that month.

I'm having trouble with the As-Of table, and I'm not sure if it's because I've not set it up properly or because it just can't do what I'm trying to do.

// ======== Create a list of distinct Months ========


Load distinct dateAsOfForm

  Resident [MasterCalendar];

// ======== Cartesian product with itself ========

Join (tmpAsOfCalendar)

Load dateAsOfForm as AsOfMonth

  Resident [tmpAsOfCalendar];

// ======== Reload, filter and calculate additional fields ========

[As-Of Calendar]:

Load dateAsOfForm,


  Round((AsOfMonth-dateAsOfForm)*12/365.2425) as MonthDiff,

  Year(AsOfMonth)-Year(dateAsOfForm) as YearDiff

  Resident tmpAsOfCalendar

      Where AsOfMonth >= dateAsOfForm;

Drop Table tmpAsOfCalendar;

AsOf table sample:

dateAsOfForm AsOfMonth MonthDiff YearDiff
2018 Jan2018 Jan00
2018 Jan2018 Feb10
2018 Jan2018 Mar20
2018 Jan2018 Apr30
2018 Jan2018 May40
2018 Jan2018 Jun50
2018 Jan2018 Jul60
2018 Jan2018 Aug70
2018 Feb2018 Feb00
2018 Feb2018 Mar10
2018 Feb2018 Apr20
2018 Feb2018 May30
2018 Feb2018 Jun40
2018 Feb2018 Jul50
2018 Feb2018 Aug60

Interest Income Month - 1

Sum({<MonthDiff = {1}>} [Loan Interest Income])

Interest Income Month - 2

Sum({<MonthDiff = {2}>} [Loan Interest Income])

Result sample:

%Loan_PK Interest IncomeInterest Income Month - 1Interest Income Month - 2

This is a pretty consistent ask from my users and I'd hate to tell them it's just not possible. I appreciate your help on this.


And your %Loan_PK dimension is still based on the original month field, right? So you're not using the AsOfMonth as dimension like explained in the article.

Can you create a small demo qlikview document with some data: Preparing examples for Upload - Reduction and Data Scrambling

talk is cheap, supply exceeds demand