For a client we are building an aging report. They prefer to look back in time.
Due to the size of the data we used an interval and data island to select the right record, back in time, Because we are dealing with invoices with several due dates and partial payments, adjustments etc.
But to determine which record falls into the right bucket, thee user need to select a date from the data island. Which creates a problem.
Because the data is not connected to the data model, we can’t determine the buckets in the back-end. We need dynamic buckets, but we have trouble creating them. The reload of the chart is over an minute right now. And since we defined that a record should fall in an certain interval. It can also mean that one record falls in different buckets in the same interval.
Does anybody have an idea, how we can maintain the data island and also use it to determine the buckets, so that the record falls into the right bucket where the user does not have to wait for a minute to reload the chart?
Thanks you in advance for helping me coming up with a possible solution.
Liesbeth
Ps: We thought of creating a loop that would create the dates between the min and max date per key of . This would take Qlik 16 hours to build. Not a great solution. (we did a test trial of 100 records)
Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
To refer the selections within the data-island is quite easy and effective with an indirect set analysis with the function p(). But if your calculated dimension with the buckets performed well or could be optimized couldn't be said with these informations - how look the datamodel and the used calculated dimension and the chart-expressions?
Buckets are usually based on the invoice date to Todays date so if you have to rely on just invioice dates to create buckets then you can connect the island date to the invoice date But if you are considering many dates to create the buckets then you have to do it through the link table like the below
load Invoice,
Invoicedate,
Billdate,
Invoice& Invoicedate & Billdate as Key
Amount
from Tablea ;
Linktable
load
Key,
Invoice_date as Date
resident Tablea ;
concatenate
load
Key,
Invoice_date as Date
resident Tablea ;
And use the Date field in the link table for your bucket calculation
We found another way. We did create dummy records for the date range and due dates per key, so we know in which date range it falls and also which bucket. Than we created a new date and compared that date with the date in the data-island.
