Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Calculation of the amount prorated by dates

Hello,

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.

Best Regards.

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Hi Maria,

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

Data:

load *

    , 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

2 Replies
Highlighted
Specialist
Specialist

Hi Maria,

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

Data:

load *

    , 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

Highlighted
Contributor II
Contributor II

Hi Tatsiania,

Thank you very much. It's just I was needing. It works perfect.

Best Regards.