topic Aggregation of expression around dimension in QlikView

Aggregation of expression around dimension

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a very large dataset presented in a table like this:

ID	Bin	Frequency	Total Frequency	%
1234	1-5	3	9	33
1234	6-10	4	9	44
1234	11-15	2	9	22
4567	1-5	8	17	47
4567	6-10	5	17	29
4567	11-15	4	17	24

ID is a dimension

Bin is a calculated dimension: =Class(Score,5)

The rest are expressions:

Count(DISTINCT Ref)

AGGR(NODISTINCT Count(Ref),ID)

Count(DISTINCT Ref)/AGGR(NODISTINCT Count(Ref),ID)

What I would like is to create additional rows, for every possible 'Bin', where the frequency for all the IDs are totalled.

In my example above, it would create 3 additional rows like this:

1-5	11	26	42
6-10	9	26	35
11-15	6	26	23

Happy to do this within the table's expressions, or in script!

All help appreciated

Re: Aggregation of expression around dimension

sunny_talwar — Fri, 16 Feb 2018 13:29:20 GMT

You want this to be shown in the same table? Also, can you create the calculated dimension in the script like this

Class(Score, 5) as Bin

Re: Aggregation of expression around dimension

jessica_webb — Fri, 16 Feb 2018 13:36:10 GMT

Yes, would like it in the same table.

Calculating it in the script would probably help with the horrendous lagging I'm experiencing. Problem is '5' is actually a variable called 'BinWidth' which can be defined via an input box.

So I guess I couldn't do that if the calculation was occurring in the script

Re: Aggregation of expression around dimension

sunny_talwar — Fri, 16 Feb 2018 13:38:57 GMT

If there is a small range of numbers the user can enter, for example, the user will ever enter 2, 3, 5, 10, 20... then you can create 5 fields and then conditionally use them... makes life little easier with the expressions....

Anyways, coming back to the requirement ... you will have 0 as ID?

Re: Aggregation of expression around dimension

jessica_webb — Fri, 16 Feb 2018 13:41:53 GMT

I'll put that to the user as that might work better... Problem is, I think they'll be using decimals, so my possible range of numbers could be huge!

Yes, would like '0' as the ID. Apologies for not explaining all this properly in the initial message!