Urgent: create dynamic table in script

[dickelsa]

Hi again,

I have a problem with working out my idea in qlikview.

So the situation is like this:

A transport company owns 30 trucks, and wants to stay at 30.

His leasecontract sais that every year he has to get 3 new trucks in lease.

this means that is ammount of trucks grow linear by 3 each year.

but

The owner does not want any more than 3 trucks, so he sells the 3 OLDEST trucks each year.

so when my built year is 2008,2008,2009,2001,2014, he will sell the trucks from 2001,2008 and 2008.

Then he leases 3 new trucks which have the built year of the Year(SelectedDate).

A mandatory is that the trucks he sells, also have ' expired'  lease contracts.

Here is what he wants to see:

When i click a random date in the future, he wants to see what trucks are stil running, and which trucks are not.

Because we do not know which licence plates these trucks have, we call them Truck A, Truck B, Truck C, Truck (...), and so on.

See attached what i allready have.

I really have no idea what to do, can you please help me?

Kind regards

Dick

[vgutkovsky]

Dick, rather than trying to do this in the script, you might have an easier time doing it dynamically in the UI. Here are the general steps I'd implement:

1. Create a date island. This should be a calendar table in the data model that is disconnected from the rest of the model. It should contain all the dates that you want to allow your user to select (i.e. SelectedDate in your example).

2. The expression to represent active trucks would be something like this:

aggr(

     if([Start date]<=min(SelectedDate) //to make sure that the lease started

         and [End date] >= max(SelectedDate) //only include trucks whose lease hasn't expired

         and rank(min({<[End Date]={"<$(=max(SelectedDate))"}>}-[Start date]),4,1)>3, //exclude 3 oldest trucks whose leases have expired

          [License Plate]

     )

     ,[License Plate]

)

There are a lot of data nuances that will affect the particulars of this solution, but that should be the foundation IMO.

Regards,

Vlad

[dickelsa]

Hi Vlad,

Yes,  I understand what you mean, but the new license plates also have to have the average monthly lease calculated to them, otherwise i cannot make a linechart what my monthly cost is going to do on the selected date.

Thanks for the new input, i just don't know how i can implement it in the script.

Regards,

Dick

[vgutkovsky]

Dick, the above was just a way you could make a list box of active license plates. However, it's a pretty easy next step to add whatever expression you want. For instance, you could create a line chart with Month as dimension and the following expression:

avg(aggr(

     if([Start date]<=min(SelectedDate) //to make sure that the lease started

         and [End date] >= max(SelectedDate) //only include trucks whose lease hasn't expired

         and rank(min({<[End Date]={"<$(=max(SelectedDate))"}>}-[Start date]),4,1)>3, //exclude 3 oldest trucks whose leases have expired

          LeaseAmount

     )

     ,[License Plate],Month

))

Vlad