Accumulation Data divide

[#1]

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

[CELAMBARASAN]

Can you upload a sample file?

[nagaiank]

Is the attached qvw file meeting your requirements?

[#4]

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

[nagaiank]

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.

[#6]

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

[nagaiank · Accepted solution]

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.

[#8]

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