Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
ersanduran
Contributor III
Contributor III

Using Alternate State in QLikSense (without Extensions)

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

20 Replies
ersanduran
Contributor III
Contributor III
Author

Hi Onur

I am not able to see the attachment, could you please add one more time?

Regards

Ersan

Not applicable

Attached Again

ersanduran
Contributor III
Contributor III
Author

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

Capture.PNG

silvacarlos
Creator II
Creator II

This is really helpful, thanks!

Not applicable

‌Hello Ersan,

thanks for the information. It was really helpful.

sogloqlik
Creator II
Creator II

Great Solution, much better than the extention

supriyaranganat
Contributor III
Contributor III

I was looking for a solution for so long.. Thank you so much... It worked... ersan.duran

ersanduran
Contributor III
Contributor III
Author

I am glad to be able to help.

vkish16161
Creator III
Creator III

All,

This feature is now available in Qlik Cloud.

Alternate states QS 2.JPG

NewToQlik
Creator
Creator

I noticed that in the example, the 'CalendarYear' filter pane allows u to select a year, even with no data. Is it possible to have some indications, eg. darken the colour of years with no data in the filter pane?