Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikwiz123
Creator III
Creator III

Count of values in the field and Average

I have data in the below given format.

        

TypeCategoryTransactionDate                         Bank                  In Date        Out DatePatternDescription
ABC CreditDeposit9/23/2018      11111
ABC DebitWithdrawal9/23/2018 11
XYZCreditDeposit9/23/2018 11111
XYZDebitWithdrawal9/23/2018 11
AAA DebitWithdrawal9/23/2018 11
AAA CreditDeposit9/23/2018 11111
ADebitWithdrawal9/23/2018 11

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.

2 Replies
swuehl
MVP
MVP

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)

qlikwiz123
Creator III
Creator III
Author

Thank you Stefan! I will give this a shot