Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mikegrattan
Contributor II

Set analysis question

Hi; I'm a newbie trying out the free versions of Qlik View and

Sense.  I'd like to see if there's a way to do a particular type of Set Analysis. 

Assuming my current selection includes products of the type PT and a particular customer and channel.  How would I compare total Sales for that selection against total sales for the same customer, same channel, but NON PT products? 

1 Solution

Accepted Solutions
Not applicable

Re: Set analysis question

Assuming you already applied selections in Customer, Channel and Product ("PT") dimensions:

1- Sales for the selected values:

     Sum (Sales)

2- Sales for the same Customer(s), same Channel(s) but for non PT Products (excluded values because "PT" is selected):

     Sum({<Customer=P(Customer), Channel=P(Channel), Product=E(Product)>} Sales)


Where P() will give you the Possible values of that field in the current state of selections and E() will give you the excluded values (the values in grey for the current selections).

9 Replies
greeshma_k
Contributor III

Re: Set analysis question

Hi,

You can write two expressions as shown below.

1. sum(Sales)

2. sum({<Product=>}Sales)

The first one will give you the Sales for all selections that you have made whereas scond one will not consider the selection on Product Type but it will give the sales for slected customer and channel.

Regards,

Greeshma

MVP
MVP

Re: Set analysis question

Sales (respecting all selections)

=Sum(Sales)

Sales ignoring customer and channel selections and only products not selected

Sum({<Customer=, Channel=, Product=E(Product)>} Sales)

MVP
MVP

Re: Set analysis question

HI,

Try like this

For Current Selections:

=Sum(Sales)

Total Sales for that selection against total sales for the same customer, same channel, but NON PT products?

Sum({<Product=E(Product)>} Sales)

OR

Sum({<Product={'Non PT Product1', 'Non PT Product2', 'Non PT Product3'.........'Non PT Productn'}>} Sales)


Sum({<Product={'NON PT'}>} Sales)

Regards,

Jagan.

Not applicable

Re: Set analysis question

Assuming you already applied selections in Customer, Channel and Product ("PT") dimensions:

1- Sales for the selected values:

     Sum (Sales)

2- Sales for the same Customer(s), same Channel(s) but for non PT Products (excluded values because "PT" is selected):

     Sum({<Customer=P(Customer), Channel=P(Channel), Product=E(Product)>} Sales)


Where P() will give you the Possible values of that field in the current state of selections and E() will give you the excluded values (the values in grey for the current selections).

yevgeniy
Contributor

Re: Set analysis question

Hi,

You can write two expressions:

sum({<Product={'PT'}>}Sales)

sum({<Product={'NON PT'}>}Sales)


regards

Yevheniy

mikegrattan
Contributor II

Re: Set analysis question

Hi folks; thanks for the great feedback!

I tried Borja's recommendation and that seems to work for doing a straight sum.  I've created a Master item with this formula and another one for Volume (shipped units).  If I want to get an average, can I add another Master item measure for a calculation like Volume/Sales?  I tried it and the result is just a null value.

mikegrattan
Contributor II

Re: Set analysis question

Not sure how to edit previous post, but that formula should actually be Sales/Volume, but you get the idea.

MVP
MVP

Re: Set analysis question

Hi,


Try like this

sum({<Product={'PT'}>}Sales) / sum({<Product={'PT'}>}Volume)

sum({<Product={'NON PT'}>}Sales) /sum({<Product={'NON PT'}>}Volume)


Regards,

Jagan.

mikegrattan
Contributor II

Re: Set analysis question

Thanks for the response.  I did already get it to work using the source formulas for the individual calculations.  I was just wondering if it was possible to use the calculation names instead of the source formulas?  If so, I haven't figured out how to get it to actually work.

Community Browser