Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Snapshot filter

Hi everybody,

I'm struggling with a filter I've been asked to implement. I don't have a lot of experience and it seems quite complex.

I have a table Accounts, let's assume these 3 accounts:

  • Acc1, creation date March 2017,
  • Acc2, creation date May 2017,
  • Acc3, creation date August 2017

The creation month would be the Month 0, the following one the Month 1 and so on...

For the Acc1: March 2017 is Month 0, April 2017 is Month 1, May 2017 is Month 2...

For the Acc2: May 2017 is Month 0, June 2017 is Month 1, July 2017 is Month 2...

For the Acc3: August 2017 is Month 0, September 2017 is Month 1, October 2017 is Month 2...

The filter would have Months from 0 to 6 (from the creation month to the next six months) and would show data for the month selected and those before. To give you an example.

If I select 2, I want to be shown Months 2, 1 and 0:

  • May, April and March 2017 for Acc1;
  • July, June and May 2017 for Acc2;
  • October, September and August 2017 for Acc3.

Any idea on how I can implement this filter? I'm completely stuck.

Thanks

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

Pseudo-Cartesian Product: Yes, that's about the quickest way to create an AsOf table for all cases. As a further reduction you could reduce the result of this table generation to include just those keys that are present in the Collections table.

To show you how this AsOf-table is meant to work, I modified your example a little bit. The idea is to channel all sections in Master Calendar and Accounts through the AsOf-table. That table contains a switch field (AsOfType) that

determines what associations get to the Collections and which do not. For example, if I select AsOfType=6 (i.e. Creation Month the the six following months) all accounts+months will link to data in the Collections table as long as the months fall within the designated period. The others are simply not present.

Best,

Peter

View solution in original post

7 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

It's not that difficult to implement, just counter-intuitive.

The technique to do this is called an ASOF-Table (actually a variation). It contains data that usually maps a single calendar month to many to get a YearToDate simulation in your data model. See HIC's treatment: The As-Of Table:

You can use this technique and create an ASOF-table that sits between your calendar (containing MonthYear values) and your facts table. The latter is linked to the ASOF-table using a MonthYear+AcctNo key.

The ASOF table contains two keys and one special field with your filter values (0 - 6) and an 'OFF' value. For every 'OFF' value, an entry in the ASOF-table maps every calendar MonthYear value to the same MonthYear value+Every Account No. A sort of pass-through entry. Of course, you only need to map the values that exist in your Facts table.

For the 7 other values, the ASOF-rows disconnect from the calendar, but map to the specified MonthYear+AcctNo values for every AcctNo. The MonthYear values correspond to the required values since the creation date of a particular account.

It is best to start filling an ASOF-table with the pass-through values. Then you add for example the 0-month values and experiment with it.

It sounds complex, until you see it work.

Peter

[Edit] Sorry for the spam. Corrected a few spelling mistakes and added some tips.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

I realise that a particular script solution may work for some object layouts but not for others. It all depends on how the user wants to select the account mode, what the impact should be on existing objects, and how new objects should handle the date ranges.

For example, if I select mode 2 (creation month, creation month+1, creation month+2) and multiple accounts are displayed, would the user like to align them on an anonymous "creation month" scale, or would the user require the aggregation results be displayed on a standard MonthYear calendar? The former cannot have a total value without some explanation (and the business sense of this display may be questionable) while the latter may cause the results to be spread accross multiple years.

The requested visualisation would impact the data model constructs. Can you provide a few more details on how the results should be displayed?

Anonymous
Not applicable
Author

Hi Peter,

thanks a lot for your answers. To reply to your last question and give you more context: the table Accounts is linked to the table Collections (the money we get from the accounts), then the goal is to show how well the accounts performed starting from the creation month and for the following six months.

Then I would say that my dashboard is in the first case ("to align them on an anonymous creation month scale")  regardless of the year, also because this dashboard contains data for the previous 18 months. Moreover, there will be a filter to select the year, in case they need it.

Anyway, today I'm working on the solution contained in your first reply, tomorrow I will let you know if it works properly.

Michael

I forgot one important thing: the Collections table contains a date as well. Then, the calculation starts from the Creation date and include the Collections for the following 6 months. If the Creation date for an account is on March 2017, I want to show the collections having dates from March till September 2017; for another account created on June 2017, I want to show the collections having dates from June till December 2017. The second date probable makes the As-of table a bit trickier.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Collections table with a date: I don't think the ASOF-table will be tricky to build, if you link to this table using MonthYear values. For example, create a new field in your Collections table based on the collections date using Date(MonthStart(), MM/YYYY') or something similar. Use that value in the key that connects to the ASOF-Table.

When you fill the ASOF Table from the Accounts table, you do the same. Start from MonthStart(Account Creation Date) and add 6 successive months using the AddMonths() function. The key will be created using the same technique as explained before.

Financial reporting is usually done in (Fiscal) Periods. So your calendar (if you have one) will probably use MonthYear or PeriodYear combinations anyway, no?

[Edit] Spelling mistakes...

Anonymous
Not applicable
Author

I think that the stakeholders need MonthYear. Anyway, I created a small qvw with a few data and fields, just to show you what I've done. I'm not sure it's correct.

For example, you said to link the AsOfCalendar and the Accounts table through a key, that I called MappingKey (EG. 123-Jan2017). In the Accounts table it was easy to create this field as there is the AccNo (named OurRef), but in the AsOfCalendar I had to load the OurRef from Accounts. I've ended up with multiplying every rows by the OurRef, a kind of cartesian product. Is this correct?

I'm attaching a small qvw with same sample data. It reflects the data model I have in this moment. If you could have a look at it, I would really appreciate it. The data model is as follows:


DataMod.jpg

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Pseudo-Cartesian Product: Yes, that's about the quickest way to create an AsOf table for all cases. As a further reduction you could reduce the result of this table generation to include just those keys that are present in the Collections table.

To show you how this AsOf-table is meant to work, I modified your example a little bit. The idea is to channel all sections in Master Calendar and Accounts through the AsOf-table. That table contains a switch field (AsOfType) that

determines what associations get to the Collections and which do not. For example, if I select AsOfType=6 (i.e. Creation Month the the six following months) all accounts+months will link to data in the Collections table as long as the months fall within the designated period. The others are simply not present.

Best,

Peter

Anonymous
Not applicable
Author

I've already implemented that solution in the first dashboard and it works perfectly. Now I have to do the same with a more complex dashboard where we have much more data and on a longer period ( last 24 months). This is my only concern but I guess it will only take more time for all the combinations.

Peter, many thanks for your big help, I was stuck on this filter for one week and really didn't know what to do.