topic Re: Top 30 merchants in QlikView

Top 30 merchants

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Top 30 merchants

swuehl — Wed, 14 Sep 2016 21:46:17 GMT

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

Re: Top 30 merchants

Wed, 14 Sep 2016 22:32:45 GMT

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

Re: Top 30 merchants

sunny_talwar — Wed, 14 Sep 2016 23:37:49 GMT

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.

Re: Top 30 merchants

Thu, 15 Sep 2016 15:03:10 GMT

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)

Re: Top 30 merchants

swuehl — Thu, 15 Sep 2016 16:16:28 GMT

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?

Re: Top 30 merchants

sunny_talwar — Thu, 15 Sep 2016 18:50:20 GMT

How about this?

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

Re: Top 30 merchants

Thu, 15 Sep 2016 21:28:19 GMT

Thanks Sunny, But it didn't work .

Re: Top 30 merchants

el_aprendiz111 — Thu, 15 Sep 2016 21:30:05 GMT

Hi,

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

'#.##0'
)

Re: Top 30 merchants

Thu, 15 Sep 2016 21:30:43 GMT

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

Re: Top 30 merchants

sunny_talwar — Thu, 15 Sep 2016 21:55:48 GMT

What if you do this?

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