topic Re: Tables: Collect data from previous dates in a single record in QlikView

Tables: Collect data from previous dates in a single record

ttollin11 — Thu, 25 Oct 2018 15:34:18 GMT

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:

[Field]

Month - 1:

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

Month - 2:

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

The variables are:

vMonthPrv1:

Max([Record Date]) - 1

vMonthPrv2:

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().

Re: Tables: Collect data from previous dates in a single record

Gysbert_Wassenaar — Thu, 25 Oct 2018 16:25:39 GMT

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

Re: Tables: Collect data from previous dates in a single record

ttollin11 — Thu, 25 Oct 2018 17:55:28 GMT

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 ========
tmpAsOfCalendar:
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,
  AsOfMonth,
  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 Jan	2018 Jan	0	0
2018 Jan	2018 Feb	1	0
2018 Jan	2018 Mar	2	0
2018 Jan	2018 Apr	3	0
2018 Jan	2018 May	4	0
2018 Jan	2018 Jun	5	0
2018 Jan	2018 Jul	6	0
2018 Jan	2018 Aug	7	0
2018 Feb	2018 Feb	0	0
2018 Feb	2018 Mar	1	0
2018 Feb	2018 Apr	2	0
2018 Feb	2018 May	3	0
2018 Feb	2018 Jun	4	0
2018 Feb	2018 Jul	5	0
2018 Feb	2018 Aug	6	0

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 Income	Interest Income Month - 1	Interest Income Month - 2
201801\|536\|BWLoan	$273.65	$273.65	$273.65
201802\|536\|BWLoan	$246.72	$246.72	$246.72
201803\|536\|BWLoan	$272.89	$272.89	$272.89
201804\|536\|BWLoan	$263.63	$263.63	$263.63
201805\|536\|BWLoan	$271.85	$271.85	$271.85
201806\|536\|BWLoan	$262.75	$262.75	$262.75
201807\|536\|BWLoan	$271.21	$271.21	$0.00
201808\|536\|BWLoan	$270.86	$0.00	$0.00

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.

Re: Tables: Collect data from previous dates in a single record

Gysbert_Wassenaar — Fri, 26 Oct 2018 06:00:46 GMT

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