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
      Jan1EntertainmentMusic50
      Jan1EntertainmentGames50
      Jan1ElectronicsMobile500
      Jan1ElectronicsHome200
      Jan1BooksAudio50
      Jan1EntertainmentMusic20
      Feb2EntertainmentMusic20
      Jan2ElectronicsHome300
      Jan2BooksAudio50
      Jan2BooksHard Copy20
      Jan2EntertainmentMusic20
      Feb2BooksHard Copy100
      Feb2EntertainmentGames100
      Jan3EntertainmentMusic150
      Feb3EntertainmentGames10

       

       

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

       

       

       

      Thanks

        • 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:

                    Capture.PNG

                     

                    !

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

                        Peter,

                        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.

                        Thanks

                        Again

                          • 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] )

                              into:

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

                             

                            Capture2.PNG

                             

                            best regards

                             

                            Petter Skjolden