I’m hoping you can help
I have a cross table which is stores down the left, category along the right. The report is to show sales and vs bud
I want to put a ranking based on the total vs bud
I then want to filter that ranking based on an input (to show, say the top and bottom 5 stores)
(vs bud is not a defined variable in the universe btw)
vs bud aka Sales Vs Budget Company
=Sum([Sales No VAT])-Sum([Budgeted Sales No VAT])
The ranking variable aka Total Company Ranking Store
=NoFilter(Rank([Sales Vs Budget Company] ForEach ([Store])) ; All)
The Top/Bottom Filter where [Top/Bottom Input] is an input control led variable
=If([Total Company Ranking Store] <= ToNumber([Top/Bottom Input]) ;
"Top " + [Top/Bottom Input] ;
If([Total Company Ranking Store] >= ((Count([Store]) In Report) - ToNumber([Top/Bottom Input])) ;
"Bottom " + [Top/Bottom Input] ;
0 ))
So all the above works fine as long as I don’t have category across the top
How do I get my formulas to ignore category attribute in the cross table?
honeyloco (BOB member since 2016-09-19)