Rank: Member
Groups: extranet\Forum
Joined: 7/12/2011(UTC) Posts: 7

Hi, how do I make a "total" calculation row/visibility row, that has different formulas, depending on which column is should take the data from? The red box should be 0 or empty, the green box is a SUM formula and the blue is a AVG formula. I only have one red box, but may need more blue then the one shown, the rest is pure SUM formula. Can I make a calcalution row, which formula take data from the different cells depending on the column? Edited by user Wednesday, September 26, 2012 1:18:09 PM(UTC)
 Reason: Image not shown




Rank: Administration
Groups: extranet\Forum
Joined: 4/7/2011(UTC) Posts: 490

Hi Lars, I am assuming that your columns are all measure columns. You can then use this formula to determine which measure column your are currently in: allcount(d1, d1, m1:0) If inserted as a calculated row, this formula will return '1' in the first measure column, '2' in the second etc. Combined with ifthenelse statements you can now produce conditional calculations like this one: Best regards, Ole Edited by user Wednesday, September 26, 2012 1:52:00 PM(UTC)
 Reason: Not specified 
Ole Dyring 



Rank: Member
Groups: extranet\Forum
Joined: 7/12/2011(UTC) Posts: 7

Hi Ole
That worked as a charm, but I have one question more... :)
If I want of make af formula in the then part, how do I type that? For instance if I want something like this: if allcount(d1, d1, m1:0) = 4 then (column 1 * column 2 / column 3) else 0




Rank: Administration
Groups: extranet\Forum
Joined: 4/7/2011(UTC) Posts: 490

I assume that with "column 1", "column 2" and "column 3" you want to refer to the previous three values of the same calculation...?
In that case the syntax would be something like this:
if allcount(d1, d1, m1:0) = 4 then sum(0, 0, m1) * sum(0, 0, m2) / sum(0, 0, m3)

Ole Dyring 



Rank: Member
Groups: extranet\Forum
Joined: 7/12/2011(UTC) Posts: 7

No, not entially. In this case, colunm 1, 2 and 3 is 3 different colunms. But perhaps I could just change m3 til the right colunm number... [update] Yes, chaning the m to the right colunm worked. [/update] Edited by user Thursday, September 27, 2012 2:24:24 PM(UTC)
 Reason: Not specified




Rank: Administration
Groups: extranet\Forum
Joined: 4/7/2011(UTC) Posts: 490

I know. Maybe I should have been more clear:
I assume that with "column 1", "column 2" and "column 3" you want to refer to the previous three values, i.e. the three other measure columns, of the same calculated row...?
If this assumption is correct, then my avove suggestion is still valid. Have you tried it?
But on the other hand  maybe I am still wrong.

Ole Dyring 



Rank: Member
Groups: extranet\Forum
Joined: 7/12/2011(UTC) Posts: 7

Ahh, you where right the first time, as allways... :)
I just misunderstood it, as you meant the three colunms I refered to in my first post.
I think I have everything covered now, just have to check all calculations. But if I need more help, I'll write it here or start a new post.
Thanks for the help.




Rank: Member
Groups: extranet\Forum
Joined: 7/12/2011(UTC) Posts: 7

Hi Ole
Just ran into another question.
In one of the formulas, I can get the situation, where it tries to divide with 0. I thought, that I could just put in a new ifthenelse inside another ifthenelse, but TARGIT will not agree with me on how to do this.
My try is something like this (a part of the full formula): if allcount(d1, d1, m1:0) = 14 then if avg(0, all, 0) = 0 then 0 else avg(0, all, 0) else if allcount(d1, d1, m1:0) = 15 then . . . .
Is it possible to have an IF inside another IF?




Rank: Administration
Groups: extranet\Forum
Joined: 4/7/2011(UTC) Posts: 490

I think you just need to put it into a couple of brackets:
if allcount(d1, d1, m1:0) = 14 then (if avg(0, all, 0) = 0 then 0 else avg(0, all, 0)) else if allcount(d1, d1, m1:0) = 15 then 
Ole Dyring 



Rank: Member
Groups: extranet\Forum
Joined: 7/12/2011(UTC) Posts: 7

As allways it is a simple solution, when you look at it. Just can't believe I didn't try with brackets myself.




You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.