topic Re: Creating dynamic buckets with a data island in QlikView

Creating dynamic buckets with a data island

Anonymous — Tue, 10 Oct 2017 20:50:52 GMT

Hello Guys,

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)

Re: Creating dynamic buckets with a data island

Anil_Babu_Samineni — Wed, 11 Oct 2017 10:03:43 GMT

Sure, Can i know the reference anything from you?

Re: Creating dynamic buckets with a data island

marcus_sommer — Wed, 11 Oct 2017 11:58:49 GMT

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?

- Marcus

Re: Creating dynamic buckets with a data island

qliksus — Thu, 12 Oct 2017 05:37:58 GMT

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

Re: Creating dynamic buckets with a data island

Anonymous — Wed, 18 Oct 2017 09:33:10 GMT

He Guys,

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.

Thanks for replying.

Have a good day,

Liesbeth