Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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:

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

Tags (1)
3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

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

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

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

Highlighted
MVP & Luminary
MVP & Luminary

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

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