Re: set analysis for total sales - Qlik Community

pallvit_01 · ‎2018-09-25

Hi,

i have a requirement where i need total sales of country Inida for year 2014 and total sales of country UK for 2015. Please follow the below details.

ABC:

LOAD * INLINE [

Year, Country, Sales

2014, IND, 200

2015, IND, 150

2014, UK, 250

2015, UK, 50

];

i want my output only 250.

how can i show this in a single expression. Note only in single set analysis expression.

felipedl · ‎2018-09-25

Hi,

Use the following expression:

sum({<Year={2014},Country={'IND'}>+<Year={2015},Country={'UK'}>}Sales)

As a result, you'll get:

Felipe.

vishsaggi · ‎2018-09-25

may be another way is like

= Rangesum(Sum({<Country = {'IND'}, Year = {2014}>}Sales), Sum({<Country = {'UK'}, Year = {2015}>}Sales))

Use this in your textobject or in straight table expression.

vishsaggi · ‎2018-09-25

Hi Felip, may be you do not need + before sales right?

felipedl · ‎2018-09-25

You do need it if your going to do the addition of set analysis (since he wanted in only one expression).

Example 2:

Sum({$<Year={2009}>+1<Country={'Sweden'}>} Sales)

This set expression contains the identifiers $ and 1, the operator + and the modifiers <Year={2009}> and <Country={'Sweden'}>.

Operators are used to include, exclude, or intersect parts of or whole data sets. All operators use sets as operands and return a set as result.

This table shows operators that can be used in set expressions.

Operator	Description
+	Union. This binary operation returns a set consisting of the records that belong to any of the two set operands.

pallvit_01 · ‎2018-09-26

Thankyou Vishwarath, Its really helpful

pallvit_01 · ‎2018-09-26

Hey Felip,

Thank you, I did not know that operators can also be used in set analysis like this which you have shown in above expression.

Best Regards.

felipedl · ‎2018-09-26

Good to know it helped.

Can you mark the correct anwser that helped you so others in the community might see it too?

Thanks

MarcoWedel · ‎2018-09-26

This refers to + being a set operator instead of an arithmetic operator like remarked.

So I think the comment is valid that "+Sales" can be replaced by just "Sales" in your expression, right?

hope this helps

regards

Marco

felipedl · ‎2018-09-27

Oh right, didnt realise i've put the +Sales at the end, then yes, it's right, there's no need for the plus sign at the end.