Skip to Content
0

Ranking and Filterineg in a Cross Table

Jan 20, 2017 at 09:59 AM

14

avatar image

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?

10 |10000 characters needed characters left characters exceeded

share your issue screenshot.

0
* Please Login or Register to Answer, Follow or Comment.

0 Answers