Min/Max issue in Crosstab WEBI

[Former Member]

minmaxissuecrosstab.png

Hi everyone,

I created a crosstab shown in the attached image. I created a Min and Max variable =Min([amount]) and =Max([amount]). They do not give the correct min and max numbers for each of the types, they just give the min and max number of the row (I'm assuming).

Any reason why this is happening? Should I not use a Crosstab, instead use a horizontal table and define each cell for the min and max of each type?

Thank you,

Kelly

[amitrathi239 · Accepted solution]

is "Kash Amount" a measure object with datatype Number?

if yes then create V Kash variable with qualification as "Dimension"

After that use =Min([V Kash]) In ([Type])

[Former Member · Accepted solution]

It is a measure object, it will not let me change it to a Dimension or a Detail.

Error is attached, also shows the formula.

kashmeasure.png

[Former Member]

I changed the Kash amt -k to a dimension, ex: Amt =[kash amt - k"

Then I did Max([Amt]) and it worked with changing the Kash amt to a dimension. Thank you!

Do you know why it had issues with the data set as measures and not dimensions?

[amitrathi239]

With measure object and sum aggregation max/min not work as values are already aggregated..changing the qualification to dimension change the aggregation..values will not be grouped..

You will get the difference if you drag both objects in some where report.

Aggregated object as measure will show you one value and dimension variables will show you many values in column

[Former Member]

Amit, there are multiple values for each type, I wanted the crosstab to summarize the min, max, and avg values for each type. Attached is some sample data.

typesandamounts.txt

[amitrathi239]

In your data screenshot i can not find multiple rows for any Type.Looking at your screenshot i can not find any data type where multiple rows for one type.

have you shared the dummy data here?

[amitrathi239]

Can you share the screenshot of your crosstab?

[Former Member]

structure.png

data.png

I attached an image of the structure - including my max variable and an image of the data.

[rajan_burad]

Hi Kelly,

If I understand your requirement clearly in Max field you want the maximum/highest value present in Total; like in Max you want $22,303.56 for all rows and in Min field you want $8.00 for all rows.

Try this:

Max: Max([Total]) ForAll ([Type])

Min: Min([Total]) ForAll ([Type])

Let us know if this helps you to achieve your requirement.

Thanks,

Rajan 🙂

[Former Member]

That returns $22,303.56, which is the max amount of the sum of all records for each type. I want BO to grab the min/max value of all the records for each type. Each type has several records, and I want the sum, count, max, min, and avg for each type.

[amitrathi239]

It’s look like correct as I did not see any multiple values for each type.this is the reason same value is appearing in min /max column.

If you have multiple values then try below formula

=min(your object) in ( Type Object)

Similar for Max variable

[Former Member]

I did use "=Max([Kash Amt - K]) In([Kash Type - K])" for my Max variable and it still returns the sum value for that type rather than looking through all the records and pulling the max value for that type.

It comes out correct when I make a vertical table and type out the types myself and use "=Max([Kash Amt - K]) Where ([Kash Type - K] = "K")", but it make my formulas messy and takes longer.