3 Replies Latest reply: Dec 5, 2017 12:07 AM by Luis Madriz RSS

    Value List for Bins?

    Jeff Catterson

      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

        • Re: Value List for Bins?
          Anil Babu

          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

          )))))

          • Re: Value List for Bins?
            Luis Madriz

            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

              • Re: Value List for Bins?
                Luis Madriz

                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