Skip to Content
avatar image
Former Member

Ranking and Filterineg in a Cross Table

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 where 5 will be the input from a user)
(vs bud is not a defined variable in the universe so there's no short cut in this case)

vs bud aka Sales Vs Budget Company
=(Sum([Sales No VAT]) ForAll ([Category+])) - (Sum([Budgeted Sales No VAT]) ForAll ([Category+]))

The ranking variable aka Total Company Ranking Store
=NoFilter(Rank([Sales Vs Budget Company] ForAll ([Category+])) ; 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

Even when they are across the top its fine... but then I try to filter out the 0s.... and it shows a completely obscure filtered result. Why?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers