Straight Table Ranking order

[johngouws]

Good day.

I have a situation where I have a dataset for a Company with many outlets, Sites, with Items. I am trying to show the top 5, 5 is set in a variable vTop, Items sold per Site in the last 7 days.

In the attached PNG file I show a straight table as well as a pivot table, both using the same expressions.

In the straight table the ranking is a calculated dimension where in the pivot table the ranking is an expression. The Company marked in black in both tables’s ranking is not correct in the straight table but is correct in the pivot.

Please can anyone check these expressions and tell me what I am doing wrong.

I am using the straight table in the final App, because of the sub-totals, but if totals can be added to the pivot table that is also a big win.

The Ranking expression is - The Itname sort uses the same ranking expression in the straight table:

=if(aggr(rank(sum({<Date={">$(=date(Today()-7))<=$(=date(Today()-1))"}>} Amt)),Site,Itname)<=$(vTop),
aggr(rank(sum({<Date={">$(=date(Today()-7))<=$(=date(Today()-1))"}>} Amt)),Site,Itname),null())

The Sales expression is:

=sum({<Date={">$(=date(Today()-7))<=$(=date(Today()-1))"}>} Amt)

Thank you in advance.

Straight and Pivot tables

[marcus_sommer · Accepted solution]

The pivot has also the feature of totals which is called partial sums and you could enable it within the tab presentation in the top left area.

Another thought goes to the missing outer-aggregation of your ranking which didn't result in an aggregation-expression else in a calculated dimension. Therefore you may adjust it in the following way:

sum(aggr(rank(sum()), Dim1, Dim2)) or maybe: aggr(NODISTINCT rank(sum()), Dim1, Dim2))

Beside this the sorting of a multi-dimensional table could become quite tricky and you may need a separate sorting-expression for each dimension.

I didn't such things since ages and don't remember my doings very well unless that I also had some struggles. I think I ended it by using calculated dimensions which already contained the needed sorting and looked probably similar to this:

aggr(dual(
    if(rank(sum(Field))<=$(var), Dim1, 'Others'), 
    if(rank(sum(Field))<=$(var), rank(sum(Field)), $(var) + 1)),
Dim1)

which enabled also the possibility to include all others as group and to display their sums and rates, too.