topic Re: Set Analysis in QlikView

Set Analysis

alwayslearning — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm not sure if this can be done via SET Analysis or something else.

I have a pivot table with a dimension on Seller (Seller) and then a column which states if they make a sale or not (Sale).

Accounts can change to a different seller over it's lifetime and I want to measure the total sales made to any account which belong to the seller dimension. I have a field which identifies current owner Current Owner).

So I would like to if(Current Owner = Seller, then sum Sales) This would be Total sales made in sellers accounts.

Re: Set Analysis

vishsaggi — Fri, 19 Jan 2018 17:44:48 GMT

Can you share some sample data in a text or excel file and your expected output, so will be easy to work on. ?

Re: Set Analysis

alwayslearning — Fri, 19 Jan 2018 18:00:26 GMT

I hope this can explain it:

Seller	Current owner	Account	Sale Week	Sale
John	Jane	Account 1	1	1
John	Jane	Account 1	1	1
John	Joe	Account 1	1	1
Jane	Jane	Account 2	2	1
Jane	John	Account 2	2	1
Joe	Joe	Account 3	3	1

My Pivot table looks something like:

Seller	Sales which belong to seller (Filtered for just week1)	Total Sales Lifetime made to the account
John	3	1
Jane	0	3
Joe	0	2

Row 3 of my first table is an good example of what I am looking to account for: The seller was John in week 1. So when I am showing week 1 data the sale is attributed to him (as in table 2), but now I say Joe owns this account and so his base of sales to his accounts include this 1. (The dimension my pivot is based on is seller)

Re: Set Analysis

vishsaggi — Fri, 19 Jan 2018 18:53:15 GMT

Ok so what is your expected output?

Try below

Dim: Seller

Expr: = Count(Sale)

= Sum({<[Sale Week]>} Sale)

1st expr is sales done by seller

2nd is total sales.

Re: Set Analysis

alwayslearning — Sun, 21 Jan 2018 14:45:57 GMT

The expected output is:

John

Jane

Joe

First column is easy and just some of Sale (with a set analysis based on Week) as dimension is seller

The calculation I am having trouble with is how many sales have been made to the account overall. I know it is a bit confusing but e.g.: Dimension John, if you look at how many accounts he current owns and how many sales have been made to that account is 1. However the problem is the dimension is seller and not current account owner, but the view I want is based on seller. i.e The Table above would be the result of the data I provided.

Re: Set Analysis

vishsaggi — Sun, 21 Jan 2018 15:49:02 GMT

Oh ok. Tricky one. I have to look into it again. Meanwhile will askstalwar1‌‌ he can put some ideas into it.

Re: Set Analysis

sunny_talwar — Sun, 21 Jan 2018 17:42:07 GMT

May be with a slight modification in the script

Table:

LOAD RowNo() as RowNum,

Seller,

[Current owner],

Account,

[Sale Week],

Sale

FROM

[https://community.qlik.com/thread/288464]

(html, codepage is 1252, embedded labels, table is @1);

LinkTable:

LOAD RowNum,

Seller as New_Seller,

'Seller' as Flag

Resident Table;

Concatenate(LinkTable)

LOAD RowNum,

[Current owner] as New_Seller,

'Current owner' as Flag

Resident Table;

Re: Set Analysis

alwayslearning — Mon, 22 Jan 2018 10:12:02 GMT

Thanks for the above. I wanted to try and avoid script edits as I pretty much finalised my script and didn't want to make to many changes there.

I was thinking I could do some kind of: count if (Current owner = seller and sale = 1), any thoughts?

Re: Set Analysis

sunny_talwar — Mon, 22 Jan 2018 10:15:38 GMT

Since this only works when you have current owner and seller on the same row... how would it work for your scenario? You almost need your dimension to be independent of seller and Current owner to do this.... You can probably create an island table and then use an if statement for both your expressions. But I was not able to do this without script intervention

Re: Set Analysis

alwayslearning — Mon, 22 Jan 2018 11:08:31 GMT

Hi Sunny,

Thanks I thought the same but just thought I'll get a second opinion. I will create a second table and join Seller with Current Owner to arrive at Total Sales to an account.