Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
indexing on based on the previous month or the first month ? are you planning to do this in script or chart ?
Always first month (Jan'07) in this case.
In a chart as the data I'm working on has been calculated in a chart.
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.
sum(Rent)
/
max(TOTAL <UnitType> aggr(sum({<YearMonth={"$(=Date(min(YearMonth),'YYYYMM'))"}>}Rent),UnitType) )
date formatting can be optional based on your date field
That's what I have tried - but only getting results for Jan'07
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)))