Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Onur
Thanks a lot for sharing the app. Now I see what is wrong. You integrated your selection tables to your data model. Data island means that you create tables for your selections but you will not associate them to your data model. In this case any selection you make on one of those selection table will not have any affect on your associative model and you will use set analysis to reflect your selection in your associative model.
So what you need to do is:
1. keep only Date B field in DetailB and Date A field in DetailA tables (remove other 2 fields)
2. load the data and confirm that there is no association between those 3 tables in the picture.
3. In front end update your formulas as follows:
Date A Total: Sum({$<Date=P([Date A])>}[Count])
Date B Total: Sum({$<Date=P([Date B])>}[Count])
The set analysis statement "Date=P([Date A])" here means that filter Count values only to Date values where Dates values are in one of the selected "Date A" values. In other words using set analysis you build the association between your selection and your data (in your case Summary table)
Please let me know if this is clear.
Regards
Ersan
