Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a special case/user requirement that in a pivot table I would show a SUM, ignore negative values from the source table BUT make one exception.
I believe a nested IF should do the trick, but all my tries so far failed; I got a bunch of different results... except of course, the one I'm looking for :-).
It looks like my syntax is correct, but my logic is wrong; I was wondering if the Community may be able to point me in the right direction.
SOURCE TABLE 1
ID | Value1 |
---|---|
1 | 10 |
2 | 20 |
3 | 1 |
SOURCE TABLE 2
ID | Value 2 |
---|---|
1 | 10 |
2 | -10 |
3 | -20 |
My Pivot Table is simple:
Dimmension: ID
Expression: Sum(Value1 * Value2)
The desired Pivot Table would look like this:
Header 1 | Header 2 |
---|---|
1 | 100 |
2 | 0 |
3 | -20 |
Basically:
1. IF(Sum(Value1*Value2)<=0,0,Sum(Value1*Value2))
2. IF(Table2.ID = '3', Sum(Value1*Value2))
How do I combine the two IFs so that it will ignore (0) all negative values in SOURCE TABLE 2 except where ID=3?
Thank you very much in advance
I think that this expression will help...
IF(ID = 3, Sum(Value1*[Value 2] ),
IF(Sum(Value1*[Value 2])<=0,0,Sum(Value1*[Value 2]))
)
See attached file for the sample.qvw that I made for this issue
I think that this expression will help...
IF(ID = 3, Sum(Value1*[Value 2] ),
IF(Sum(Value1*[Value 2])<=0,0,Sum(Value1*[Value 2]))
)
See attached file for the sample.qvw that I made for this issue
Thank you very much Roberto for your quick reply; I have tried your expression and it works to some degree.
What I mean by that is the numbers in the Combined column are OK, but when I do a Total for the ID dimenssion, the result is 0, when it should be 80.
Either I'm missing something, or this is kinda strange behaviour from QV.
You may want to try moving your sum aggregation outside of the IF statement.
COMBINED Column
sum(IF(ID = 3, Value1*[Value 2],
if(Value1*[Value 2]<=0
,0
,Value1*[Value 2]
)
)
)
I've attached Roberto's sample with the modified formulas.
If you want that it ignores only the negative values in TABLE 2, except when ID=3, then .......
Sum(If(ID=3,Value2*Value1,If(Value2<=0,0,Value2*Value1)))
Ah, I didn't notice the incorrect total. Datawitch indeed seems to have the corrected version for you 🙂
Absolutely great stuff; Datawitch expression does give me the result I want. I struggled with this for a few days and now I look at the solution and it seems so simple! Not a good feeling for me :-).
Thank you very much everyone for the help!
PS Apologies to Datawitch as I can only have one correct answer.
No worries, I'm glad I was able to help. I ran into that a few months ago myself.