Formula to select max in a calculated field (Crosstab CR2019)

[ashrafsharaf]

Hello,
I am doing a cross tab that is supposed to show item sales per month and the average sales for months that have quantities greater than 0, totals, and then the on hand stock.

So far i was able to achieve everything except for the on hand, i have the correct quantitiy coming out of the query(multiple times per item so i need to select max/min) but i am unable to include it with the desired visualization.

Check the attached screenshot for clarification, i need to add the red column just like that

Thanks in advance,

Ashraf

[DellSC · Accepted solution]

This won't export to Excel very well, but it should work to view on the screen or print what you need.

1. Add the max of OnHand to the regular fields in the cross-tab to get the final totals to appear. So you'll really have two fields for each month - inventory and on hand.

2. Once it's in the cross-tab, right-click on it and check "Suppress" for the monthly numbers.

3. Make the header for the monthly numbers blank.

4. Make the monthly field and header objects as narrow as you can and take out the line between on hand and the monthly number.

-Dell

[ashrafsharaf · Accepted solution]

That actually worked although it becomes problematic if you export to excel as you said (or you're trying to color fields it leaves a very small white gap because i couldn't completely suppress month numbers.

I actually ended up doubling my query with a 2nd UNION that gets onhand as quantity and 13 as month, and inserted the average and total before it so it looks like this:

Thanks for the help :),

Ashraf