BusinessObjects Board

Formula based on range from other columns

building reports webintelligence
#1

Hi,

I have below data in report. Qty is measure and all other objects are dimension objects.

image

Qty 15 is falling in range from 0-15 so it is flagged as ‘X’
and Qty 20 is falling in range from 16-20 so it is flagged as ‘X’

so if we filter above table on Flag = ‘X’ then we have below report
Cat Subcat Qty From TO Discount% Flag
C1 SC1 15 0 15 2 X
C1 SC2 20 16 20 3 X

My issue is, if I remove SubCat column, From, TO columns from above block then I am expecting below report

Cat Qty Discount% Flag
C1 35 4 X

Since aggregated Qty is 35 and it is falling under the range from 21 to 50, so expecting discount% value 4 in above block…

appreciate your quick help…

Prem

#2

I’m assuming you have just two quantities, 15 and 20, and that the other rows are duplicates of the same measure

If that is the case just create logic that references the quantity

eg something along the lines of

if quantity is greater than 20 then 4 else if quantity is greater than 15 then 3 else 2

#3

You’ll need to use the ForEach calculation context.

If you remove [Sub Cat] from your block, you can add it back into a formula.
So you’d have [Discount%] ForEach([Sub Cat]) and you’d still get the rows you want.