Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anil_Babu_Samineni

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

)))))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

3 Replies
Anil_Babu_Samineni

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

)))))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
luismadriz
Specialist
Specialist

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
Specialist
Specialist

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