Count of values in the field and Average

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

I have data in the below given format.

Type	Category	Transaction	Date	Bank	In Date	Out Date	Pattern	Description
ABC	Credit	Deposit	9/23/2018	1	1	1	1	1
ABC	Debit	Withdrawal	9/23/2018	1				1
XYZ	Credit	Deposit	9/23/2018	1	1	1	1	1
XYZ	Debit	Withdrawal	9/23/2018	1				1
AAA	Debit	Withdrawal	9/23/2018	1				1
AAA	Credit	Deposit	9/23/2018	1	1	1	1	1
A	Debit	Withdrawal	9/23/2018	1				1

I need to enable the user to select the filters on Type, Category, Transaction, and Date and based on the selections, I need to check if the subsequent fields have '1' in the values. After that, I need to sum them up and find the average by number of fields.

For example, if the user selects Type - ABC, Category - Credit, Transaction - Deposit , Date - 9/23/2018, I need to count how many fields have '1' in them and find the average as below.

5 (1 for Bank, 1 for In Date, 1 for Out Date, 1 for Pattern and 1 for Description) / 5 (Since there are 5 fields) =1

If the user selects none, it should count number of '1's in all the 5 fields divided by number of fields, which is 5.

Re: Count of values in the field and Average

swuehl — Sun, 23 Sep 2018 10:24:01 GMT

If I understand your request correctly, you could transform your table from a crosstable to a straight table using

The Crosstable Load

Something like

CROSSTABLE (FlagField, Flag, 4)

LOAD Type, Category, ..... // all other fields like shown above

FROM YourTable;

Then your count could look like

=Count({<Flag = {1}>} DISTINCT FlagField) / Count(DISTINCT FlagField)

Re: Count of values in the field and Average

qlikwiz123 — Mon, 24 Sep 2018 14:12:55 GMT

Thank you Stefan! I will give this a shot

topic Re: Count of values in the field and Average in QlikView

Count of values in the field and Average

Re: Count of values in the field and Average

Re: Count of values in the field and Average