topic Accumulation Data divide in QlikView

Accumulation Data divide

Fri, 11 Jan 2013 21:19:38 GMT

Hi Everyone,

I have monthly revenue data and monthly booking data. I need to show them accumulate data month by month. I need create a ratio o those two data. Here is accumulate booking data:

if(sum ( {$<[Month] = {"<=$(vReviewMonth)"}>} [Bookings]/1000000),

rangesum(

sum ( {$<[Month] = {"<=$(vReviewMonth)"}>} [Bookings]/1000000),

above("BookingActual")

))

Here is Revnue Data:

if(sum ( {$<[Month] = {"<=$(vReviewMonth)"}>} [Bookings]/1000000),

rangesum(

sum ( {$<[Month] = {"<=$(vReviewMonth)"}>} [Bookings]/1000000),

above("BookingActual")

))

I am looking to get right Ratio:

Month	1	2	3	4	5	6	7
Revenue	3	4	5	7	6	4	5
Booking	7	5	3	6	4	5	7
Accumulate Revenue	3	7	12	19	25	29	34
Accumulate Booking	7	12	15	21	25	30	37
Ratio	2.333333	1.714286	1.25	1.105263	1	1.034483	1.088235

If I use [booking data]/[Revenue Data], I cannot get right ratio. Anybody can help?

Thank you inadvance,

Dust

Re: Accumulation Data divide

CELAMBARASAN — Fri, 11 Jan 2013 21:40:33 GMT

Can you upload a sample file?

Re: Accumulation Data divide

nagaiank — Fri, 11 Jan 2013 23:38:37 GMT

Is the attached qvw file meeting your requirements?

Re: Accumulation Data divide

Sun, 13 Jan 2013 18:12:14 GMT

Hi Krishmamoorthy,

Thank you very much for your helpful answer. I need show this ratio only by a line chart. The user also need be able to select the month and the chart should show a line from month 1 to month selected. It looks very complex.

Let me know if you have any thoughts on it.

Thank you again,

Dust

Re: Accumulation Data divide

nagaiank — Sun, 13 Jan 2013 19:36:03 GMT

Modified application to show the ratio and other numbers as line graphs is attached.

If you want to selected months only, you may change the dimesion to a calcuated dimension with appropriate expression as needed.

Hope this helps.

Re: Accumulation Data divide

Sun, 13 Jan 2013 20:03:16 GMT

Thanks, Krishnamoorthy!

I can see your beautiful solution here. But when I select a month, the chart need show all the month from begin of the year.

For example, I want to see month 4, can the chart show 1,2,3,4 as a line chart?

I appreciate your help.

Best,

Dust

Re: Accumulation Data divide

nagaiank — Sun, 13 Jan 2013 20:20:16 GMT

I have made changes to the qvw file to do what you stated above. See if this meets your needs. Basic data exists in the tables generated by the script. You need to play with calculated dimensions, expressions and other UI object to implement what you want.

Hope this helps.

Re: Accumulation Data divide

Sun, 13 Jan 2013 20:48:57 GMT

Hi Krishnamoorthy,

Thank you very much for your solution. I can see you beautiful thoughts. My current is a little bit complex. Would you be able to do a anyway meeting to help me out on this?

Thanks,

Dust