Alternative Measures / Dimensions or Filters & Set Analysis

[Anonymous]

Hello,

I am currently discovering QlikSense and I don't know how to use all its features yet. I need some help using Bar Charts (or histograms) and alternative measures / dimensions.

I have imported data from a csv file. A sample of the data would look like this :

Product	Sales_id	Date	Value
P1	id1	2017-05-01	10
P1	id2	2017-05-01	3
P1	id2	2017-05-01	5
P1	id4	2017-05-02	8
P1	id5	2017-05-02	15
P2	id1	2017-05-01	1
P2	id4	2017-05-02	2
P2	id6	2017-05-03	4

I want to create a bar chart that will give the number of product sold per day for a specific product, but I want to be able to select the product on the chart. Here is the illustration of the result I would expect :

I think the pseudo code would be something like :

    if PRODUCT = 'P1'

          then X = Date

                  Y = Number of sales per day = Count( Sales_id )

And for the alternative measure, the code would be the same but with the condition PRODUCT = 'P2', which would allow the user to select the product on the Y axis.

Is it possible to do this with QlikSense, and if so, how ?

Any help would be greatly appreciated.

Thanks a lot !

Haroun

[Gysbert_Wassenaar]

The easy way out is to simply select the Product value you want to see the bars for. Add Product as dimension to the bar chart. Then simply select the Product value you wish to see. Unfortunately removing the selection can't be done from the chart itself. So you may want to add a Filter Pane object for the Product dimension instead and make the selections in the Filter Pane instead.

[Anonymous]

Thanks for the tip. I ended up using a filter pane because I didn't find any other option, but I wanted to use a solution more "script oriented", based on this structure : Count( {<Product = {'P1'} >} sales_id) but with the possibility of adapting this for any value of the dimension Product.

For another example : let's say I want to get the sum of all values for the minimum date of the table, I would write it like this :

Sum({<date = {min(date)} >} Value) or maybe even Sum( if(date = min(date), Value )

which would result here in 10+5+3+1 = 19

But the problem in my example seems to be min(date) which is not interpreted by QlikSense, and only the string '2017-05-01' does work but I want to be able to do this without knowing the minimum date of my table for this calculation.

Any further help on this topic would be appreciated thanks a lot

[Gysbert_Wassenaar]

Perhaps your date fields doesn't contain dates, but text strings that look like dates. In that case you need to convert those text strings to date values first using the Date# function: Date#(date, 'YYYY-MM-DD') as date.

[Anonymous]

I finally found the way to make it work. The proper expression is as follows :

Sum({<date = {'$(=min(date))'} >} Value)

If I understood correctly the principle, it works like that :

- the '' are required because Dates are interpreted as string

- the $(= ... ) is needed to calculate the expression

Since I also figured out the way to get the sum of value for a specific date range, I will put it there in case some people are wondering how to do it.

Example : 2 variables are set, vMinDate for the minimum and vMaxDate for the maximum date of the interval we want to calculate.

To get the sum of the value between vMinDate and vMaxDate, you can use this expression :

Sum( {< date = { '>=$(=$(vMinDate))' , '<=$(=$(vMaxDate))' } >} Value)

The underlined part is the equivalent of the logical "OR", but if you want to select the attributes that fit both criteria ("AND"), do not add the 'comma'.

If my explanations weren't clear enough, or just for informative purposes on Set Analysis, I HIGHLY recommend these 2 videos on this topic by Michael Tarallo mto‌‌ which sums up very well how to get started with set analysis and gives a good glance of its possibilities :

Beginners' Introduction to Set Analysis - Qlik Sense and QlikView - YouTube

Set Analysis - Part 2 - Cool Stuff You can Do: Qlik Sense and QlikView - YouTube

Thanks again for the help and consideration.

Best regards,

Haroun