Full Accumulation and Percents - please help someone

[#1]

I've got such an issue. I have "Sales per day" and "Marge per day" in my Qlikview report and I also use FULL ACCUMULATION with both of them. Now I need to add one more column which will be counted with use of previous two columns as "Marge per Day"/"Sales per day" and I need this field also to be with FULL ACCUMULATION. I tried to use Inter-record function Column() to evaluate this third column but when I use it, it ignores FuLL ACCUMALATION option. And when I use "Marge per Day"/"Sales per day" in expression and turn on FULL ACCUMULATION of course it just sum all results which is not what I need. Can anybody give me a piece of advice what to do?

[#2]

If you name/label your first expression "one" and your second expression "two" you can add a second expression that equals one/two. Then check Full Accumulation. Maybe this will help.

[#3]

It didn't work. Maube I should explain it a little bit more clearily. Imagine I have data for three days about Sales and Marge

	Monday	Tuesday	Wednesday
Sales	5	10	5
Marge	2	6	1

And I want to add one more column with MargeCoeff,for instance, which will be counted like this :

MargeCoeff for Monday = Marge for Monday / Sales for Monday

MargeCoeff for Tuesday = (Marge for Monday+Marge for Tuesday)/(Sales for Monday+Sales for Tuesday)

MargeCoeff for Wednesday = (Marge for Monday+Marge for Tuesday+Marge for Wednesday)/(Sales for Monday+Sales for Tuesday+Sales for Wednesday)

And I still can't find the way to do this 😞

[#4]

I believe your going to have to use some form of set analysis. So take Tuesday for example, the equation might look like:

MargeCoeff for Tuesday = Count( {$<SalesDayOfWeek={"<=$(=2 )"}>} Sales) / Count( {$<=MargeDayOfWeek={"<$(=2)"}>} Sales)

This assumes you have a DayOfWeek field so the answer kind of depends on how you have your dates set up.

[#5]

Hi patcionov,

Check attached file, tell me if that helps.

[#6]

is this what you're looking for?

[#7]

Sorry,

A few parentheses missing. This is you answer. The expression is:

(Marge+rangesum(above(Marge,1,
rowno())))
/
(Sales+rangesum(above(Sales,1,
rowno())))

[#8]

Ah ok, that works too. Easy thing to miss, I do that a lot.

[#9]

thank that's working. But I didn't see this correction/ Instead of this I just used Inter-record frunction Column and that was the formula I used - Column(1)/Column(2). Thank you very much

[#10]

Hi Moshe & Pactionov,

Just wrote out the individual Sales & Marge for quick data validation. Ultimately, I guess you only want the %age value. Have fun.