13 Replies Latest reply: Aug 21, 2017 4:32 PM by Carlos Silva RSS

    Using Alternate State in QLikSense (without Extensions)

    Ersan Duran

      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.

       

      Capture.PNG

       

      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