Top 30 merchants

[#1]

I took Merchant as dimension and sum(Sales) as my expression. And also suppressed the null values for Merchant. I got the total as 123030099402.72.

I want to display the sum(Sales) of the top 30 merchants by Sales in a text box as well.

So I did =sum({<Merchant = {"=Rank(sum(Sales)) <=30"}>}Sales).

But I got a very weird value of 32428895761.17. Is it because of nulls.

What is the correct expression in a text box for sum(Sales) for largest 30 merchants excluding nulls?

[swuehl]

The overall total seems to be larger than the TOP30 Merchant total. Isn't this expected?

[#3]

In the straight table, I used dimension limits and I'm only showing values for largest 30.

[sunny_talwar]

Try this same expression (this one -> =Sum({<Merchant = {"=Rank(Sum(Sales)) <=30"}>}Sales)) in a straight table with Merchant as dimension to see if you see any difference between this new straight table vs the straight table you created using dimension limits.

[#5]

Hi Sunny,

Its returning the same value but I can't tie up the numbers when I'm suppressing the values in the straight table.

=Sum({<Merchant  -=  {' '},Merchant = {"=Rank(Sum(Sales)) <=30"}>}Sales)

[swuehl]

I believe you can't use two field modifiers on the same field within the same set modifier (pair of <...>), I think only the second will be considered.

Why do you need to exclude Merchant ' '? Can't you clean your data in the load script instead?

[sunny_talwar]

How about this?

=Sum({<Merchant = {"=Rank(Sum({<Merchant  -=  {' '}>}Sales)) <=30"}>}Sales)

[#8]

Thanks Sunny, But it didn't work .

[el_aprendiz111]

Hi,

     =
      NUM(
           Sum(
            {<
                Merchant =
                       {"=RANK(SUM({$<Merchant={'*'}-{''}>}Sales),4)<=30"}
             >}
          Sales),

      '#.##0'
)

[#10]

Looks like the condition works for straight table but not for text box.