Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Can you share some sample data in a text or excel file and your expected output, so will be easy to work on. ?
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)
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.
The expected output is:
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 |
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.
Oh ok. Tricky one. I have to look into it again. Meanwhile will askstalwar1 he can put some ideas into it.
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;
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?
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
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.