:? Im trying to aggregate on various conditions in side by side columns.
Problem 1: Need 4 Columns
Apples Rev|Oranges Rev|Bananas Rev|Apples/ Oranges/ Bananas|
The first 3 columns work fine by using:
= Where (=“Apples”), etc.
When the conditions get more complex I have issues. For Column 4, I’ve tried:
= If ( InList (“Apples” , “Oranges” , “Bananas”)) Then
and
= Where ( InList (“Apples” , “Oranges” , “Bananas”))
Both give errors.
Problem 2: Dynamic date conditions - To avoid hard-coding in report:
Ex: Where(Year()=Year(CurrentDate()))
I couldn’t get this to work but Year()=Year(CurrentDate()) works by itself, returning True/False. Do I have to turn this into a boolean variable to do this? Can anything else work,Sum/If maybe?
Where statements must compare to one value. So InList doesn’t work; nor does a function on the right side of the equation. Trying making some variables that will return true or false (simplying restating your current Where clause should suffice) and then use those in a Where statement…
For example, set = InList “Apples” , “Oranges” , “Bananas”) and then the equation to where = “TRUE”.
Thank you. Do you know if this is the only option? I am concerned because if I make all of these different variables then these will all have to be added to the universe for others to run my report, right?
Maybe. The variables are saved in the report itself. So if a user opens up the exact report you are creating, the variables will still be there. If the user tries to create a new report and needs to use the same logic, then it would make more sense to have the objects in the universe.
But that doesn’t sound like part of your requirement. You should be fine.
How does this differ from having a crosstab with your Products running across the top and a summary column on the right? You can filter a single crosstab to just include the products you want the side-by-side comparison on and not have to maintain anything other than the filter.
(BOB member since 2002-06-25)