topic Re: set analysis for total sales in QlikView

set analysis for total sales

pallvit_01 — Tue, 25 Sep 2018 12:00:34 GMT

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.

Re: set analysis for total sales

felipedl — Tue, 25 Sep 2018 15:55:18 GMT

Hi,

Use the following expression:

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

As a result, you'll get:

Felipe.

Re: set analysis for total sales

vishsaggi — Tue, 25 Sep 2018 16:35:53 GMT

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.

Re: set analysis for total sales

vishsaggi — Tue, 25 Sep 2018 16:37:21 GMT

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

Re: set analysis for total sales

felipedl — Tue, 25 Sep 2018 16:41:34 GMT

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

From help: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

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

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.

Re: set analysis for total sales

pallvit_01 — Wed, 26 Sep 2018 04:41:43 GMT

Thankyou Vishwarath, Its really helpful

Re: set analysis for total sales

pallvit_01 — Wed, 26 Sep 2018 04:44:35 GMT

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.

Re: set analysis for total sales

felipedl — Wed, 26 Sep 2018 17:03:13 GMT

Good to know it helped.

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

Thanks

Re: set analysis for total sales

MarcoWedel — Wed, 26 Sep 2018 21:30:37 GMT

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

Re: set analysis for total sales

felipedl — Thu, 27 Sep 2018 11:25:52 GMT

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.