topic Re. :Re: Re. :Can't Use Accumulated Numbers in an Expression? in QlikView

Can't Use Accumulated Numbers in an Expression?

Thu, 21 Apr 2011 13:10:45 GMT

I'm very new to QlikView, so please excuse the "newbie" question...

I have a straight table that has 5 columns: Date, Daily Actual, MTD Actual, MTD Budget, Difference

I used the "Full Accumulation" function for the MTD (month-to-date) numbers, and they display correctly. When building the expression for the Difference column, I find that when I use the MTD numbers in an expression, i.e.: [MTD Actual] - [MTD Budget], the expression returns the difference in the daily numbers, ignoring the accumulation.

How can I get around this?

Re. :Can't Use Accumulated Numbers in an Expression?

Thu, 21 Apr 2011 13:22:01 GMT

Hi Sam,

My point of view is that you may use the Sum function in the Expression instead of the Full Accumulation.

For this, go to your straight table properties, in the "Expression" tab.

Click on your expression [MTD Actual]. in the "Definition" on the right, write the formula "=Sum([MTD Actual])

Do this for all your expressions.

For the "Difference" column, write the formula "=Sum([MTD Budget])-Sum([MTD Actual])"

Hope this helps you.

Kindly,

Re. :Can't Use Accumulated Numbers in an Expression?

Thu, 21 Apr 2011 14:36:27 GMT

First, thank you for the very quick reply!

Here are my expressions (using the real field names this time):

DailyActual: Sum(Admit), "No Accumulation"

MTDActual: Sum(Actual), "Full Accumulation"

MTDBudget: Sum(TotalCases), "Full Accumulation"

Difference: Sum(Admit) - Sum(TotalCases), "No Accumulation"

Both MTD columns are correct, but the results in the Difference column are incorrect:

Date DailyActual MTDActual MTDBudget Difference

3/1/2011 15 15 13 2

3/2/2011 10 25 26 -3

3/3/2011 14 39 39 1

I thought I'd try something before hitting "reply"...I changed the Difference column from "No Accumulation" to "Full Accumulation". Now the results are correct!

Date DailyActual MTDActual MTDBudget Difference

3/1/2011 15 15 13 2

3/2/2011 10 25 26 -1

3/3/2011 14 39 39 0

Thanks again for leading me in the right direction!

Re. :Re: Re. :Can't Use Accumulated Numbers in an Expression?

Thu, 21 Apr 2011 14:54:23 GMT

Your welcome ;-),

I think that you don't need "Accumulation". If you put "No accumulation" for all, it must works also.

If your problem is solved, you can check "Verify Answer". Other users will knoqw that there's a solution in your post ;-))

Good strat with QlikView !!!

Kindly,