Create concatenated field based on values from previous dates from a time series based table

[Anonymous]

I have a table called "Dynamic" that has a list of information for stores over time. All time intervals are month end values. The table is unique on the combination of Store ID and Data As of Date (month end date value).

In this table I have a field called Delinquency.  This represents how many months Delinquent the store is on paying its rent. So if the store has missed the last two months of rent then it would be equal to 2.  If the store is not delinquent then the value is 0.  I want to have the ability to look at a string that would be the concatenation of the Delinquency field for the last 12 months at each Data As of Date for each Store.

Here were my initial thoughts

• No updates to the loading script and use set analysis on the final dashboard. Some how the set analysis would only select the last 12 months of data. The problems I anticipated is that the user wants to see this 12 character string as of today's date as well as previous dates in time. So can't just use a Today() function.  Also not sure if set analysis can work with a concatenation

• Use a series of for loops in the loading script to go through the "Dynamic" table. It would be 3 nested loops. It would loop through each store then loop through each data as of date and then loop  through the last 12 data as of date values. This sounds like this would be very inefficient.

• My final idea that I went with was to order the table by Store ID & Data As Of Date and then use a series of of peak & previous functions. Since the table is ordered, I peek at the previous rows to see if it is still the same Store ID and if so grab the previous Delinquency value.  I have to go as far back as 11 rows before the current row. See code below.  FYI- the 12 character string will have the most recent date as the rightmost character. If there is no Deliquency value for a previous date then I output "".

Load

[Store ID],

[Data As Of Date]

TEXT(

  TEXT(if(peek([Store ID],-11)=[Store ID],Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Delinquency))))))))))),''))

& TEXT(if(peek([Store ID],-10)=[Store ID],Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Delinquency)))))))))),''))

& TEXT(if(peek([Store ID],-9)=[Store ID],Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Delinquency))))))))),''))

& TEXT(if(peek([Store ID],-8)=[Store ID],Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Delinquency)))))))),''))

& TEXT(if(peek([Store ID],-7)=[Store ID],Previous(Previous(Previous(Previous(Previous(Previous(Previous(Delinquency))))))),''))

& TEXT(if(peek([Store ID],-6)=[Store ID],Previous(Previous(Previous(Previous(Previous(Previous(Delinquency)))))),''))

& TEXT(if(peek([Store ID],-5)=[Store ID],Previous(Previous(Previous(Previous(Previous(Delinquency))))),''))

& TEXT(if(peek([Store ID],-4)=[Store ID],Previous(Previous(Previous(Previous(Delinquency)))),''))

& TEXT(if(peek([Store ID],-3)=[Store ID],Previous(Previous(Previous(Delinquency))),''))

& TEXT(if(peek([Store ID],-2)=[Store ID],Previous(Previous(Delinquency)),''))

& TEXT(if(peek([Store ID],-1)=[Store ID],Previous(Delinquency),''))

& TEXT(Delinquency)

) as Last12Months

Resident Dynamic

order by [Store ID], [Data As Of Date] asc;

This last method does work but I wasn't sure if there was a faster/efficient way. This dynamic table is only going to grow as we add more dates and more stores.

Thank you,

Brian

[crusader_]

Hi Brian,

If your final result should be something like below:

Store ID    Last12Months

1               000000043210

2               000000000000

3               000210000000

Try below Chart expression:

=CONCAT({<AsOfDate={">=$(=only({<AsOfDate={'$(=AddMonths(Max(AsOfDate), -12))'}>} AsOfDate))<=$(=only({<AsOfDate={'$(=Max(AsOfDate))'}>} AsOfDate))"}>} Delinquency, '', -AsOfDate)

NOTE 1: Make sure your dates are converted into single format, so you're not trying to compare 43212 with '01/02/2018'.

NOTE 2: For trouble shooting use text objects with selected StoreID, split huge expression on parts:

     =only({<AsOfDate={'$(=AddMonths(Max(AsOfDate), -12))'}>} AsOfDate) - (A) gives you the date which is -12 months to selected (or today if not selected)

     =only({<AsOfDate={'$(=Max(AsOfDate))'}>} AsOfDate) - (B) gives you selected date

Then this expression makes more sense:

CONCAT({<AsOfDate{">=$(A)<=$(B)"}>} Delinquency, '', -AsOfDate)

the second parameter on concat function is delimiter;

the third is a sort order (negative descending, positive ascending)

NOTE 3: In some versions of QlikView (haven't tested this on Qlik Sense) search "<=" works as "<" ensure you cover this as well.

Hope this helps to resolve your inquiry or at least makes step closer to solution.

Andrei

[Anonymous]

Hi Andrei,

Thank you for your response and your detail. I am new to Qilk so it's greatly appreciated.

I understand the logic of the set analysis that you are using. You are limiting the Dates to only be values that are less than 12 months of the most recent date using the Max(AsOfDate).  As a new user can you explain a little more about the intricacies of the syntax used.  I was wondering why we need to add the "only" function and you can't just have somehting like the following:

CONCAT(<[AsOfDate]={">={'$(=AddMonths(MAX([AsOfDate]), -12,1))'   <={'$(=Max([AsOfDate]))'}"}> Delinquency, '', [AsOfDate])

But besides that, this formula is actually outputting the Deliquency values for all previous months. So not just restricting to the last 12 months. Trying to troubleshoot this. Not sure what you mean by text objects. Different than a "Text & Image" chart right?

I also changed the sort order to be positive to output the order I was looking for.

Thank you,

Brian

[crusader_]

Hi Brian,

No special need to use ONLY function in this case... I don't know what was thinking about when doing so... It doesn't hurt, but your solution is simpler

ONLY function used when you need to get some specific value (single value) under certain conditions with set analysis... It would make sense if I use =only({<Population={$(=max(Population))}>} City) - show the city with Max population... but in my case it's just find AsOfDate where AsOfDate=Max(AsOfDate), which basically Max(AsOfDate)

If it shows more than 12 digits, then it means that condition >= doesn't work, might be format is different to your column... For instance you store date as number (check in data model viewer), Max() - always return integer (number), Addmonth() returns date...

Text Objects - Right Click on empty space ->New Sheet Object ->Text Object

Just place = and type your formula in text area to get result (make sure something selected, so there is no ambiguity)

Hope this helps.

//Andrei