topic Re: Count frequency of values calulcated in QlikView

Count frequency of values calulcated

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

Hello, I have a data set basic description:

Date,ID,Value

and a Mapping Table:

ID, Weight

I want to then create a table that has field called CALCCOUNT with integers 0-100

I want to then count the number of dates where sum of the round((Value*Weight),0) for that date equals CALCOUNT

Example Chart I would like:

CALCOUNT	Frequency of Dates
0	1
1	25
2	4
...	45
99	55
100	23

Thank you for your help!

Re: Count frequency of values calulcated

sunny_talwar — Tue, 24 Apr 2018 18:31:42 GMT

May be this

Dimension

Aggr(round((Value*Weight),0), ID)\

Expression

Count(DISTINCT ID)

Re: Count frequency of values calulcated

billuran — Tue, 24 Apr 2018 18:34:57 GMT

Thanks Sunny, I still need to show integers with 0 counts.

Re: Count frequency of values calulcated

sunny_talwar — Tue, 24 Apr 2018 18:49:19 GMT

Uncheck 'Suppress Zero Values' on the presentation tab and see if that works

Re: Count frequency of values calulcated

sunny_talwar — Tue, 24 Apr 2018 21:01:52 GMT

What's wrong with this one?

Re: Count frequency of values calulcated

sunny_talwar — Tue, 24 Apr 2018 21:02:58 GMT

None of my suggestions worked for you? or what I said made so sense whatsoever?

Re: Count frequency of values calulcated

billuran — Tue, 24 Apr 2018 21:13:47 GMT

They did not work, but I did bad job explaining. Here i simplified it a bit:

Date,ID,Values

I want to then create a table that has field called AGGRValue with integers 0-100

I want to then count the number of dates where the sum of values for each total date equals the AGGRValue

I did RecNo() as AGGRValue

Autogenerate (100);

So if 55 dates sum to 99 each then I want the frequency of dates to be 55 and where 25 dates equal the sum of 1 each it will show 25

Example Chart I would like:

AGGRValue	Count the number of dates that equal teh AGGRValue
0	1
1	25
2	4
...	45
99	55
100	23

Thank you for your help!

Re: Count frequency of values calulcated

sunny_talwar — Tue, 24 Apr 2018 21:16:47 GMT

Okay then try this

Dimension

Aggr(Sum(Values), Date)

Expression

Count(DISTINCT Date)

Re: Count frequency of values calulcated

billuran — Tue, 24 Apr 2018 21:24:31 GMT

Thanks Sunny, this kind of worked. I need to show an array of values regardless if the values are present. IE: 1-100.

Whats happening now as an example, 2 is never a sum of values in the data set but i need the end user to see that 2 has 0 dates associated when they export the data. Right now it will show values in a table 0,1,3,4 etc...with appropriate counts.

Re: Count frequency of values calulcated

sunny_talwar — Tue, 24 Apr 2018 21:28:38 GMT

I don't have the data to test it, but you might need an Island table with all possible values... lets call it AllValues

Dimension

AllValues

Expression

Sum(Aggr(If(Sum(Value) = AllValues, 1, 0), Dates, AllValues)) + Sum({1} 0)

Now uncheck 'Suppress Zero Values' on the presentation tab...

Re: Count frequency of values calulcated

billuran — Tue, 24 Apr 2018 21:43:33 GMT

Sunny as always you the man!! Works perfectly!