Using Alternate State in QLikSense (without Extensions)

[ersanduran]

Hi Qlik Community

I have been checking the Community to see whether there is any way to use Alternate State in QlikSense. I saw that Christof Schwarz developed an extension which looks pretty impressive (you can view video here: Comparative Analysis - Alternate States with Qlik Sense), but I do not want to depend on extensions due to various reasons.

I think I finally figured out a way to use Alternate State in QlikSense using P() function and data islands. It requires a little bit of work and is not as easy and flexible as in QlikView but I believe it will satisfy the comparative analysis needs of QlikSense users. At the end of the day, for more complicated analysis QlikView is the right tool to use anyway.

I used bike sales data (available in Microsoft's web site as a sample SQL Server DB) for my analysis. In my Dashboard I want to have a line chart where I can compare monthly sales figures of one country to the other dynamically based on user selection (it can be also 2 countries vs 3 countries etc.)

The first step is to create data islands which will serve as Selections in Dashboard. I want to compare sales in one (or multiple) country to another country, so I will need 2 data islands with list of values of Countries in my data model. I will call my new tables as GeoA and GeoB with 2 fields in each as Sales Country A and B, and Region A and B respectively.

Second step is to create KPIs that will represent the user selections from Selections A and B. I will have 2 KPI values defined as follows:

    Sales A: Sum({$<SalesTerritoryCountry=P([Sales Country A])>}Sales)

    Sales B: Sum({$<SalesTerritoryCountry=P([Sales Country B])>}Sales)

In brief what SET analysis does here is that it sets the values of SalesTerritoryCountry field in the data model to the values of user's selection from Sales Country A and B fields respectively (the field represented as data island in the data model).

As a result we can achieve the line chart as seen below where 2011 Sales figures by month in Germany vs UK can be compared.

As an additional part, I added the second chart where sales figures from one country (Germany in this case) can be compared to the average of the remaining countries in the list. This can be achieved using E() function in SET analysis.

I attached the QVF application to the post so that you can analyse my solution. I am looking forward to hearing your feedback. Please let me know if you have any questions.

Ersan Duran

[Anonymous]

Brilliant piece of work. Well done.

[ersanduran]

thanks a lot.

[juliangol]

Gracias por tu ayuda funciona perfecto!!!!

[ersanduran]

thanks a lot, I am glad i am able to help

[guruprem]

You are Qlik star. Thanks for the very inspiring post.

[tcarolus]

I'm glad I discovered this post a week ago ... Just want to thank you for sharing this!

[#8]

Hello,

Thanks for the post.  I am trying to compare different date ranges with your script.  I created the Islands and tried to sum the required field.  But when I select DateB,  the sum for "filter DateA" changes.

Eg.  Date A = 09.08.2017 >>> SumA = 12345

If I select Date B as 08.08.2017 while Date A is selected as 09.08.2017 then SumA = 12334

My expressions are as follows.

Sum({$<;Date=P([Date A])>}[Materialcount A])

Sum({$<;Date=P([Date B])>}[Materialcount B])

Could not find out why the final summed value changes when I select a second date?  Any suggestions?

[ersanduran]

Hi Onur,

If you use the logic as I described, your values should not be affected by each other. I noticed that you have semi comma in set analysis syntax, I am not sure if this was a typing error here in message or in your syntax as well but you should not use semi comma in set analysis. If you can copy your application or data set, i can help you to solve the issue.

[#10]

Hello Ersan,

Attached you can find a sample application with the same problem.

Just filter DateA and after that Filter for DateB to see that the value in DateA changes.

Thanks for your support.