Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 😞
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.
Hi patcionov,
Check attached file, tell me if that helps.
is this what you're looking for?
Sorry,
A few parentheses missing. This is you answer. The expression is:
(Marge+rangesum(above(Marge,1,
rowno())))
/
(Sales+rangesum(above(Sales,1,
rowno())))
Ah ok, that works too. Easy thing to miss, I do that a lot.
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
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.