topic Cumulative YTD in QlikView

Cumulative YTD

Tue, 09 Mar 2010 12:49:49 GMT

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!

Cumulative YTD

Tue, 09 Mar 2010 12:57:00 GMT

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.

Cumulative YTD

Tue, 09 Mar 2010 15:03:13 GMT

Exactly what I was looking for, thanks!

Cumulative YTD

Mon, 29 Mar 2010 17:25:12 GMT

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

Cumulative YTD

johnw — Mon, 29 Mar 2010 17:48:46 GMT

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

Cumulative YTD

Mon, 15 Nov 2010 10:33:30 GMT

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.

Cumulative YTD

Mon, 15 Nov 2010 10:41:27 GMT

Use Accumulation in the lower left of your expression tab.

Cumulative YTD

Mon, 15 Nov 2010 10:50:20 GMT

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.

Cumulative YTD

Wed, 24 Nov 2010 18:41:02 GMT

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