Calculations from within another data table in Qlikview

[anthony_kinsell]

Hi,

I have a number of data tables in a qvw file which contain details of rents per month for a group of apartments.

My start date is Jan'07 & I want to index all the monthly rents to date to that date with Jan'07 being 100& eg if rent in Jan'07 was 1,000 & in Feb'07 it was 800 - the index for Feb'07 is 80%. If this was excel it would simply be a $ on the cell that relates to Jan'07 - is there a similar function in Qlikview or do I have to use set analysis to keep part of the formula as Jan'07?

The table will be set out as below:

UnitType	YearMonth	Jan-2007	Feb-2007	Mar-2007	Apr-2007
2 Bed		100.00	101.00	99.97	100.00
Studio		100.00	100.00	100.05	100.00
CPI		100.00	100.50	100.75	101.00

[#2]

indexing on based on the previous month or the first month ? are you planning to do this in script or chart ?

[anthony_kinsell]

Always first month (Jan'07) in this case.

In a chart as the data I'm working on has been calculated in a chart.

[sudeepkm]

you may have the MonthYr in expression and then use Column(1)/Colmn(2) and so on for calculating the diff between other month and the first month.

[#5]

sum(Rent)

/

max(TOTAL <UnitType> aggr(sum({<YearMonth={"$(=Date(min(YearMonth),'YYYYMM'))"}>}Rent),UnitType) )

date formatting can be optional based on your date field

[anthony_kinsell]

That's what I have tried - but only getting results for Jan'07

[anthony_kinsell]

Ok basically what I need to do is replace this formula (the bold elements):

(Sum({<UnitType = {'Studio'}>}Amount)/Count({<UnitType = {'Studio'}>}DISTINCT(UnitDesc)))

/

(Sum({<UnitType = {'Studio'}, YearMonth = {'Jan-2007'}>}Amount)/Count({<UnitType = {'Studio'}, YearMonth = {'Jan-2007'}>}DISTINCT(UnitDesc)))

To this - but incorporate a min(YearMonth) in the bottom part - can anyone help? i.e. so the bottom part of the formula always picks the data from the 1st YearMonth:

(Sum({<UnitType = {'Studio'}>}Amount)/Count({<UnitType = {'Studio'}>}DISTINCT(UnitDesc)))

/

(Sum({<UnitType = {'Studio'}>}Amount)/Count({<UnitType = {'Studio'}>}DISTINCT(UnitDesc)))