Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cattersj
New Contributor

Value List for Bins?

So first off, Super new to Qlik.  I am using Qlik Sense.  My experience is 10 years front end OBIEE Developer with some back end design work.  I am ok with SQL (I get the job done) especially on the front end.  Currently, I have access to the data load editor in Qlik, but I really need to do this "binning" on the front end.

So here is my ask/brick wall.

I have a huge list of IDs that have made transactions and in one view I want to group those views by volume.  So (imagine bar chart) you would see the number of IDs that made 1, 2, 3, 4, or >4 transactions for the selected period of time.  My support person usggested I use a Value List in Qlik Sense.  What built was simple:

=ValueList('1', '2', '3', '4',  '>4')

I then referenced the value list in a metric count.

What I am basically trying to do is the following:

IF(ValueList('1', '2', '3', '4',  '>4')='1'.

Bring back the Count of all IDs that have Interactions =1 where that field that drives # of Interactions looks like this

Count( Distinct {<[transaction happened?]={1}>} [activity])

My challenge is what is the syntax?  In SQL I could write a case statement where I would say give me the count of all ID where the # of transactions = 1

Any help is amazingly appreciated.

JC

1 Solution

Accepted Solutions

Re: Value List for Bins?

You said, You are good in SQL. And this question related SQL (DB) only. So, Are you asking this question in QS? If, So

IF(ValueList('1', '2', '3', '4',  '>4')='1', Count( Distinct {<[transaction happened?]={1}>} [activity]),

IF(ValueList('1', '2', '3', '4',  '>4')='2',  Measure,

IF(ValueList('1', '2', '3', '4',  '>4')='3', Measure1,

IF(ValueList('1', '2', '3', '4',  '>4')='4', Measdure2,

IF(ValueList('1', '2', '3', '4',  '>4')='>4', Measdure3

)))))

3 Replies

Re: Value List for Bins?

You said, You are good in SQL. And this question related SQL (DB) only. So, Are you asking this question in QS? If, So

IF(ValueList('1', '2', '3', '4',  '>4')='1', Count( Distinct {<[transaction happened?]={1}>} [activity]),

IF(ValueList('1', '2', '3', '4',  '>4')='2',  Measure,

IF(ValueList('1', '2', '3', '4',  '>4')='3', Measure1,

IF(ValueList('1', '2', '3', '4',  '>4')='4', Measdure2,

IF(ValueList('1', '2', '3', '4',  '>4')='>4', Measdure3

)))))

luismadriz
Valued Contributor

Re: Value List for Bins?

Hi Jeff,

Try this:

  1. Create a table in Qlik Sense
  2. In Dimension write something like this =AGGR(Count(TransactionField),IDField)
  3. In Measure write =Count(IDField)
  4. This should give you the whole breakdown of how many transactions with how many IDs
  5. You can convert that table into a Bar charts

Now to limit your table to only 1,,4,>4

You could do this:

  1. Create another table
  2. In Dimension write something like this

=If(AGGR(Count(TransactionField),IDField) = 1,Dual('1',1),

    If(AGGR(Count(TransactionField),IDField) = 2,Dual('2',2),

       If(AGGR(Count(TransactionField),IDField) = 3,Dual('3',3),

          If(AGGR(Count(TransactionField),IDField) = 4,Dual('4',4),Dual('>4',5)))))

  1. In Measure write =Count(IDField)
  2. This should give you the breakdown for the selected frequencies
  3. And you can drag a Bar chart on it and convert it

I hope this helps,

Give and go and let us know,

Cheers,

Luis

luismadriz
Valued Contributor

Re: Value List for Bins?

Hi Jeff,

I just tested it on an App I'm working on and it works fine.

I made a little change to reduce the code. Instead of

=If(AGGR(Count(TransactionField),IDField) = 1,Dual('1',1),

    If(AGGR(Count(TransactionField),IDField) = 2,Dual('2',2),

       If(AGGR(Count(TransactionField),IDField) = 3,Dual('3',3),

          If(AGGR(Count(TransactionField),IDField) = 4,Dual('4',4),Dual('>4',5)))))

You can use

=IF(AGGR(Count(TransactionField),IDField)<=4,AGGR(Count(TransactionField),IDField),Dual('>4',5))

Cheers,

Luis

Community Browser