topic Count If Expression in QlikView

Count If Expression

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

Hello All, Here is my scenario. I have a file with multiple dates and values. I want to count the frequency of the total values for each date.

Date	Volume
1/1/17	1
1/1/17	2
1/2/17	2

so the totals for each date:

Date	Volume Total
1/1/17	3
1/2/17	2

Now I have another table where it is a list of values 0-1000 which i want to use to create a chart like below:

Values	Count of values from table above
1	0
2	1
3	1

How do i create this in script and expressions?

Message was edited by: Bill Uran

Re: Count If Expression

Anil_Babu_Samineni — Mon, 27 Feb 2017 17:10:41 GMT

Here, How data similar to countv (01-01-2017 = 0 / 1 / .... / 1000)

Can you provide Data in dates try a luck

Count({<date = P(countv)>}date)

Re: Count If Expression

billuran — Mon, 27 Feb 2017 17:41:13 GMT

I edited my question for further detail.

Re: Count If Expression

maxgro — Mon, 27 Feb 2017 18:00:56 GMT

Try in a chart with

dimension aggr(sum(Volume), Date)

expression count(aggr(sum(Volume), Date))

Re: Count If Expression

billuran — Mon, 27 Feb 2017 19:15:44 GMT

Massimo this is great but it will only show values that exist, i want the dimension values to come from a separate table that goes from 0-1000, with the field called Values.

Re: Count If Expression

maxgro — Tue, 28 Feb 2017 12:49:37 GMT

let's my try in the script

// test data

load * inline [

Date, Volume

1/1/17, 1

1/1/17, 2

1/2/17, 2

2/2/17, 3

3/2/17, 1

3/2/17, 2

3/2/17, 3

3/2/17, 4

20/2/17, 1

20/2/17, 10

21/2/17, 5

21/2/17, 6

];

// separate table from 0 to 1000

LOAD

rowno()-1 as Values

AutoGenerate 1001;

tmp:

LOAD

Date, sum(Volume) as SumVolume

Resident s

Group By Date;

left join (t)

LOAD

SumVolume as Values, count(SumVolume) as CountValues

Resident tmp

group by SumVolume;

DROP Table tmp;

final:

NoConcatenate LOAD

Values,

Alt(CountValues,0) as CountValues

Resident t;

DROP Table t;

Re: Count If Expression

billuran — Wed, 01 Mar 2017 21:04:45 GMT

Worked Perfect, thank you.