topic Re: To retrieve previous dimension variable in QlikView

To retrieve previous dimension variable

Fri, 03 Aug 2012 16:43:01 GMT

Well I am trying to project sales based on current qtr or month sales

i have 2 dept A and B and they have revenues per month till mar lets assume.

I have 2 slider objects which say if revenue increases by 20% Month on Month then what will be Business year end Revenue.

For Eg:

Dept A Actual Revenue Projected Revenue

Jan 10000 10000

Feb 15000 15000

Mar 20000 20000

Apr 0 1.2*20000

May 0

i am unable to sum the Actual Revenues by month (which is variable) i am using the below expression i have used it with quotes without quotes it still doesnt work. i tried hard coding for specific month and it returned the sum correctly but it shows lets say Feb months value for all the month. How to retrieve previous columns data and use it with below expression. i am surely missing something becoz if([Month Booked]=column(r1) is always returning false. Have tried before also no luck .

V_Month=Valuelist('Jan','Feb'...etc)

Sum({<[Month Booked]={'$(V_Month)'}> [Net Revenue])

Re: To retrieve previous dimension variable

ektarathi — Fri, 03 Aug 2012 17:32:45 GMT

Do you want to sum field "Net Revenue' by Month?In that case, what I think is, you are using the variable in ur dimension, and [Month Booked], field in ur expression, and they are not related.If you use your dimension as [Month Booked], istead of the variable and use this expression

= sum( If ([Month Booked] ='Jan',[Net Revenue], If ([Month Booked] ='Feb', [Net Revenue])))

I think you will get sum of actual revenue month wise.

But the vice versa won't work, because the variable that you have created, and [Net Revenue] field is not related anyway.

Hope this helps.

Re: To retrieve previous dimension variable

Sun, 05 Aug 2012 05:43:27 GMT

HI Ekrathi,

You are rite i am trying to get a relation between the variable V_Month and field Month_Booked. the reason being field month booked could have null values for a particular month Feb as there could have been no bookings on that month for a particular product.

In my case i want to show actual revenues and projected revenues hence i had to use a variable to create all months.

in a month where i have no revenue actual would be 0 and projected could be 20% of previous months actual revenue.

Isnt there any way you can retrieve previous columns field value and then sum the revenues for each row if previous columns value = month booked???