Cumulative YTD

[#1]

I want to create a bar chart with a cumulative YTD profit per month. I've included a simple Excel example of the input. I calculate the expression (sum of the profit) with the following formula:

Sum

(if (InYearToDate (Date, Today(), 0), [Profit]))

Now I would like to add the dimension Year-Month (2010 JAN, etc.), but the I get the profit per month. (JAN: 1M, FEB: 2M, MAR: 1.5M) and not the cumulative profit per month (JAN: 1M, FEB: 3M, MAR: 4.5M).

Can anybody help me with this change? Thanks in advance!

[#2 · Accepted solution]

Hi Anja,

I'm not sure if my solution is what you want, please ckeck it and let me know if it helps you.

Best regards.

[#3]

Exactly what I was looking for, thanks!

[#4]

Hi,

In my project I have a similar Requiremnt and I was able to resolve it using the Accumulation, however, I had another requirement to Bucket the Data based on Accumulation. So gng with your example,

If my Profit < 200000 THEN 1 ELSE 2. So O/p should be like

YearMonth	Profit	Bucket
	4500000
2010ene	1000000	1
2010feb	3000000	2
2010mar	4500000	2

How to Write the Expression in QV in such case. I am using the Expression as If (Column(1) < 20000,1,2) attaching the sample file.

Any help on this?

Regards,

Amit

[johnw]

Profit = rangesum(above(column(1)),sum(Profit)) // no accumulation
Bucket = if(column(1)<200000,1,2)

[#6]

HI! Miguel

The solution you have provide is perfectly fine but i have one more requirement. when i clik on the month Feb the sum(Profit ) here gives the value 2M. Where i my case it should still be the cumulative sum of jan and feb i.e 3M.

Is there any way to achive this by doing any changes at the script level or at the UI level

Can anyone provide any help on this.

[#7]

Use Accumulation in the lower left of your expression tab.

[#8]

Hi! Anja,

that is fine ...but when i click on a particular month i see that data for that particular month . I want to see the cumulative sum. like in your case when i clik on feb the value of profit becomes 2m i want it to be 3m.

[#9]

Hi Rahul,

Sorry for my late reply. If you're still dealing with this issue you might want to try this solution: http://community.qlik.com/forums/t/17597.aspx