Typical Requirement

[#1]

Hi,

I have data like below

Period Start	Period End	COST
3/1/2013	6/30/2013	400,000.00
7/3/2013	10/31/2013	350,000.00
11/2/2013	1/29/2014	390,000.00

And I need to show in below format

Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13	Jan-14
-	-	100,000.00	100,000.00	100,000.00	100,000.00	87,500.00	87,500.00	87,500.00	87,500.00	130,000.00	130,000.00	130,000.00

The formula which is used to calculate Cost for each month is= Cost/(Month calculate between [Period Start] and [Period End])

Is this possible in Qlikview or not

[Anonymous]

Adeel

Looks like you are need of the IntervalMatch() function.

This Blog Post IntervalMatch by Henric Cronström explains it well.

Also refer to this post which may assist you.

• Re: How to "left joining" using some criteria

Best Regards,     Bill

[#3]

Thank you Bill for your quick reply.

Its look that you didn't understand my requirement.

These are not two different tables first table is my data and i want to show in qlikview which is shown in the second table.

I want to show average monthly cost in qlikview (Straight Table)

[#4]

Bill's answer is very OK in fact.

1) Create an extra column: CostPerMonth = sth like Cost/(Month(period End)-Month(Period Start))

2) Create a calendar from the first date (min(Period Start)) to the last date (max(Period End)) : each month would be necessary (you need it if you want to display the months in clumns or rows)

3) Link each date to the Cost via IntervalMatch

Fabrice

[valerio_fatatis]

Hi,

i share this document, very easy...

IntervalMatch and Slowly Changing Dimensions