Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alwayslearning
Creator
Creator

Set Analysis

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.

9 Replies
vishsaggi
Champion III
Champion III

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

alwayslearning
Creator
Creator
Author

I hope this can explain it:

     

SellerCurrent ownerAccountSale WeekSale
JohnJaneAccount 111
JohnJaneAccount 111
JohnJoeAccount 111
JaneJaneAccount 221
JaneJohnAccount 221
JoeJoeAccount 33

1

My Pivot table looks something like:

SellerSales which belong to seller (Filtered for just week1)Total Sales Lifetime made to the account
John31
Jane03
Joe02

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)

vishsaggi
Champion III
Champion III

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.

alwayslearning
Creator
Creator
Author

The expected output is:

Seller

       Sales which belong to seller

   (Filtered for just week1)

Total Sales

Lifetime made to the account

John31
Jane03
Joe02

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.

vishsaggi
Champion III
Champion III

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

sunny_talwar

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;

Capture.PNG

alwayslearning
Creator
Creator
Author

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?

sunny_talwar

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

alwayslearning
Creator
Creator
Author

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.