How can I compare records from same dataset

[#1]

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

Month	CustomerId	Product Category	Sub Category	Amount Spend
Jan	1	Entertainment	Music	50
Jan	1	Entertainment	Games	50
Jan	1	Electronics	Mobile	500
Jan	1	Electronics	Home	200
Jan	1	Books	Audio	50
Jan	1	Entertainment	Music	20
Feb	2	Entertainment	Music	20
Jan	2	Electronics	Home	300
Jan	2	Books	Audio	50
Jan	2	Books	Hard Copy	20
Jan	2	Entertainment	Music	20
Feb	2	Books	Hard Copy	100
Feb	2	Entertainment	Games	100
Jan	3	Entertainment	Music	150
Feb	3	Entertainment	Games	10

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

[#2]

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

[Alexander_Thor]

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.

[#4]

Thanks Alex

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

[petter]

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:

!

[#6]

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

[petter · Accepted solution]

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

best regards

Petter Skjolden

[#8]

Peter,

That was helpful too. I also agree with the hack part (as I asked for it). I didn't understand the 'Totals' thing and thought it will get too complex/slow for a 4-5 level hierarchy. Anyway, I don't need totals for now (I am working around those).

I have another issue, if I use date instead of month the set expression does not work. I am attaching  a slightly modified example.

Another small thing, it seems qlik likes '1.1.2015' as the date format (as opposed to  '1/1/2015'). if I use  'MM/dd/yyyy' format none of the date functions work ( it seems I am missing something really simple).

[petter]

You have to put the $(....) within single or double quotes like this:

Sum(  {<CustomerId={$(=Concat(DISTINCT CustId1,','))},  TxnDate={"$(=TxnDate1)"} >} [Amount Spend] )

The data being returned have special characters so you will have to use quotes .... that is the safest always
but I was a bit lazy when I gave you the example earlier.... sorry about that.

[petter]

You have to declare the right date format in the top of your load script (all uppercase M D and Y):

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

...

...

...

Furthermore ensure that you replace the periods with slashes in the dates in the INLINE LOAD:

Sales:

LOAD

*

INLINE [

TxnDate, CustomerId, ProductDim, Amount Spend

01/01/2015, 1, EN-M, 50

01/01/2015, 1, EN-G, 50

....

....

....