Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

pallvit_01
New Contributor II

set analysis for total sales

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.

9 Replies
Partner
Partner

Re: set analysis for total sales

Hi,

Use the following expression:

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

As a result, you'll get:

sample.png

Felipe.

vishsaggi
Esteemed Contributor III

Re: set analysis for total sales

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
Esteemed Contributor III

Re: set analysis for total sales

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

Partner
Partner

Re: set analysis for total sales

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/SetAnalys...

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.

OperatorDescription
+Union. This binary operation returns a set consisting of the records that belong to any of the two set operands.
pallvit_01
New Contributor II

Re: set analysis for total sales

Thankyou Vishwarath, Its really helpful

pallvit_01
New Contributor II

Re: set analysis for total sales

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.

Partner
Partner

Re: set analysis for total sales

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

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

Partner
Partner

Re: set analysis for total sales

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.