Set analysis question

[mikegrattan]

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? 

[Anonymous]

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

[jonathandienst]

Sales (respecting all selections)

=Sum(Sales)

Sales ignoring customer and channel selections and only products not selected

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

[jagan]

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.

[#5 · Accepted solution]

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]

Hi,

You can write two expressions:

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

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

regards

Yevheniy

[mikegrattan]

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]

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

[jagan]

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]

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.