Discussion board where members can get started with QlikView.
I'm calculating the cost of a car per day and month, I have got different costs which are per days and others per month, for example the insurances.
I would need to know how much is the cost every day.
For instance, I have an insurance from 15/06/2017 to 15/12/2017 with a price of 500€ for whole period and from 16/12/2017 to 16/06/2018 is 600€ the period.
I would need to know the cost for each day and be able to add it to the others costs that it has.
That is, divide the total ammount per days and then associatte it. Being able to filter by dates/months/weeks with the Master Calendar.
Related to the last example,
- if I've got from 15/06/2017 to 15/12/2017 (183 days) for 500€, if I select a day, the cost would be (500€/183 days)=2'73€/day
- if I've got from 16/12/2017 to 16/06/2018 (182 days) for 600€, if I select a day, the cost would be (600€/182 days)=3'297€/day
I select 14/12/2017 -> 2'73€
17/12/2017 -> 3'297€
Is it possible to do it? If yes, what function would I have to use?
I'm able to filter the begining or ending date but not get one date between them.
Thank you very much for your support.
I created two examples for you... you can do calculation in the script level or objects
if you want to do everything in script, you'll need to generate some additional data
, date(Date_From + IterNo() -1 ) as Date
, Period_Price/(Date_To-Date_From) as Price
, Date_To-Date_From as Days
While num(Date_From) + IterNo() -1 <= num(Date_To);
load * inline [
Type, Period_Price, Date_From, Date_To
Insurance, 500, 15/06/2017, 15/12/2017
Insurance, 600, 16/12/2017, 16/06/2018
and here is example with standard objects:
1. create a standard calendar object with variable (in my case it's vDay)
2. create an object that you want with an expression sum(if(date(vDay)>=Date_From and date(vDay)<=Date_To, Period_Price/(Date_To-Date_From) ,0))
If you provide us some sample, it'll much better
View solution in original post
Thank you very much. It's just I was needing. It works perfect.