12 Replies Latest reply: Jan 5, 2015 9:34 PM by Ade Jgo RSS

    How can I compare records from same dataset

    Ade Jgo

      I want to compare shopping habits of two customers. A simple data sample data is laid-out


      MonthCustomerIdProduct CategorySub CategoryAmount Spend
      Jan2BooksHard Copy20
      Feb2BooksHard Copy100



      And I want to create a view where I can compare two customers (for a period of time). The view would look something like





        • Re: How can I compare records from same dataset

          find the sample application in attachment for solution.

          and in Qlik sense there is no alternate state option till now,so we can not do comparative analysis in Sense

          • Re: How can I compare records from same dataset
            Alexander Karlsson

            Hi Ade,


            I see people keep posting QlikView examples for you.

            In QlikView it exists a functionality called Alternate States that let's you put the data model into different selection states and allows you to achieve the functionality you are after. It requires a fair bit of knowledge around how QlikView works and the expression syntax.


            For Qlik Sense the full capability of Alternate States has not been incorporated into the product yet as we wanted to build a more intuitive way of doing the same thing but without having to do a lot of complicated expressions.

            So stay tuned for further updates of Qlik Sense as comparative analysis is on the road map.

              • Re: How can I compare records from same dataset
                Ade Jgo

                Thanks Alex


                Is there any way of doing so in Qlik Sense ? (even a hack is fine for time being).

                  • Re: How can I compare records from same dataset
                    Petter Skjolden

                    Yes there is a way of doing it in Qlik Sense - although you can't do what is termed by Qlik as Comparative Analysis you can do comparative analysis using set expressions (often referred to as Set Analysis).


                    Here is a screenshot of the Qlik Sense application I have attached:




                      • Re: How can I compare records from same dataset
                        Ade Jgo


                        Thanks a lot for your help. That got me quite far. I have couple more things to work thru

                        1. How can I display hierarchical  data in the table  i.e. I want to group data at 'Product Category'-> 'Sub Category'  level. The 'TOTAL' business we were doing in expressions didn't seem right to me. I tried 'drill down dimension' but that doesn't seem to help in table. In my real data this is 4-5 levels deep.

                        2. I have added another field (Month) to the Set expression. The behavior I want is 'match all, if none is selected'. For example, if I select customer 1 and customer 2, it should match these customers for all months (currently it does not match for any). and if I select 'Jan' for Customer 1 and nothing for Customer 2 it should match Jan of customer 1 against all months of Customer 2.


                        I am attaching a modified version of your sample.



                          • Re: How can I compare records from same dataset
                            Petter Skjolden

                            Happy to help.

                            1. You asked for a hack ... and you got it ... so it might not seem right... but the result is correct isn't it? If you are a bit more specific about what you mean by "didn't seem right to me" .. I might be able to help you. The solution I suggested is one way (maybe the only way) to have hierarchical data in Qlik Sense. This was in fact

                              a simple hierarchy of only two levels so it could be extended.

                            2. This one is straight forward: Concat() string aggregate function comes to the rescue:

                              Modify the:

                              Sum(  {<CustomerId={$(=CustId1)},  Month={$(=Month1)} >} [Amount Spend] )


                              Sum({<CustomerId={$(=CustId1)},Month={$(=Concat(DISTINCT Month1,','))} >} [Amount Spend])


                            I have attached a modified version of your last app which employs technique in #2 correctly...




                            best regards


                            Petter Skjolden