topic Re: Typical Requirement in QlikView

Typical Requirement

Tue, 10 Dec 2013 11:49:27 GMT

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

Re: Typical Requirement

Anonymous — Tue, 10 Dec 2013 12:21:11 GMT

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

Re: Typical Requirement

Tue, 10 Dec 2013 12:37:27 GMT

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)

Re: Typical Requirement

Tue, 10 Dec 2013 13:39:14 GMT

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

Re: Typical Requirement

valerio_fatatis — Tue, 10 Dec 2013 13:42:25 GMT

Hi,

i share this document, very easy...

IntervalMatch and Slowly Changing Dimensions