If Statement

system · March 24, 2009, 12:31pm

Hi,

I am using BOxi r2 and coming up to Financial Year End, my lovely users have decided they want to change the way they report.

What I had before was on different tabs there would are If statements that group a load of customer numbers together. These would split them into to different groups such as ‘territory groups’ and ‘field sales groups’. This was all fine as they were on different tabs but now they want them on the same tab and within the same block so that they can sum the values together. The problem is, is that within these groups some of the customer numbers appear on both. With the If statement, as soon as criteria is matched it will ignore it if it is repeated again elsewhere.

Does anyone know of a workaround for this or a way to ‘force’ BO to calculate differently? Unfortunately due to time constraints I need a quick fix rather…

Thanks in advance.

-------------Example-----------------

At the moment…

Tab 1

Field Sales

Group 1 ------£10,000
Group 2-------£8,000

[Field Sales] is made up of =If ([Cust No] InList (1;2;3;4);“Group 1”; If([Cust No] InList (5;6;7;8);“Group 2”))

Tab 2

Territory Groups

Group A-------£12,000
Group B-------£6,000

Territory Groups is made up of =If ([Cust No] InList (1;3;5;7);“Group A”; If([Cust No] InList (2;4;6;8);“Group B”))

Problem

Tab 1 (there is only 1 tab now)

Group 1…£10,000
Group A…£2,000
Total…£12,000

Group 2…£8,000
Group B…£1,000
Total…£9,000

And what I would like to see

Group 1…£10,000
Group A…£12,000
Total…£22,000

Group 2…£8,000
Group B…£6,000
Total…£14,000

So because of the If statement taking customer number = 1 as being in Group 1, when customer number = 1 is repeated in Group A, it is being ignored because the criteria has already been met, but I want these to be repeated.

I hope this makes sense. Please fire over any questions.

Fishbone (BOB member since 2007-06-27)

system · March 24, 2009, 12:41pm

If you combine both IF()s in one, what will happen ?

aniketp (BOB member since 2007-10-05)

system · March 24, 2009, 12:53pm

It takes the first correct criteria and ignores it when it is repeated elsewhere. So if customer ‘1’ had £3,000 worth of revenue, if you were to write

=If([Cust No] =1; “Group 1”; If([Cust No] = 1; “Group A”))

Then the results would be

Group 1…£3,000
Group A…£0
Total…£3,000

This is how it should work, which in the past has helped greatly, however I want to show.

Group 1…£3,000
Group A…£3,000
Total…£6,000

Fishbone (BOB member since 2007-06-27)

system · March 24, 2009, 1:00pm

Is there more objects you are taking in the report with this table?

I am confused how it is calculating $ amount… and how the above formula is used…?

I think you need to provide a formula for measure object , and here I think we are trying to put a formula for Dimension object. Am I right ?

aniketp (BOB member since 2007-10-05)

system · March 24, 2009, 3:21pm

The measure object is just a basic field on the database, which is summed when aggregated. So for customer 1.

Trans No.       Cust No.           Revenue
1                   1              £400
2                   1              £1,000

Fishbone (BOB member since 2007-06-27)

system · March 24, 2009, 3:39pm

First Create a variable:

Measure=Sum([Revenue] ForEach ([Cust ID]))

Now create a variable with IF()

Group=If([Cust_Id]=1;"Gorup A")

Now create one more variable:

Final=If([Group]="Group A";[Measure])

Display Group and Final in a vertical column and see if this is working…

aniketp (BOB member since 2007-10-05)

system · March 24, 2009, 4:05pm

I do wonder why your users want the data shown this way as it seems to me that the total of Group 1 and Group A would be a meaningless figure as some of the data would be double-counted (unless I’ve misunderstood the requirement) but…
Given that you’re already hard-coding the groups on the existing report, could you not create a variable for revenue for each group using the where operator, then create a variable for the sum (Group 1 + Group A). Show the three variables on separate rows for each dimension (if there are any), insert a column to the left which has ‘Group 1’, ‘Group A’, ‘Total’ in the appropriate rows. It’s a bit long-winded and a pain as it’s hard-coded but may be want you want.

PGL (BOB member since 2007-09-27)