Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

marco_puccetti
Contributor

Indirect Set Analysis

Hi i'm new to Qlikview and i need a further explaination about p() and e() functions.

I've read on the guides the meaning of these two functions but i'm really interested in there real functionality.

In this example we can omit the p operator?

sum({$<CompanyName = p({$<CategoryName={'Baby Clothes'}>}

CompanyName) - p({$<CategoryName={'SportsWear'}>}

CompanyName)>} Sales)

In particular can you explain me the difference between:

p({$<CategoryName={'Baby Clothes'}>}CompanyName)

and

{$<CategoryName={'Baby Clothes'}>}CompanyName

and you can explain me the same thing form the e function?

Thank you

Marco

1 Solution

Accepted Solutions

Re: Indirect Set Analysis

You can't leave out the p operator. This isn't correct syntax: {$<CategoryName={'Baby Clothes'}>}CompanyName. Nor would sum({$<CategoryName={'Baby Clothes'}>}CompanyName) be since names are not something to aggregate. It's not clear what you actually meant. I'll try to explain things a bit below.

Some sample data:

CompanyCategorySales
ABaby Clothes10
ASportsWear20
BBaby Clothes15
BDresses25
CSportsWear15
CDresses20

sum({$<Category={'Baby Clothes'}>}Sales) will return the sum of sales for only Baby Clothes. It will return this per company:

CompanySum of Sales
A10
B15

Company C didn't sell any Baby Clothes so it is not shown in the table.

sum({$<Company = p({$<Category={'Baby Clothes'}>} Company) >} Sales) will return the total sales for each company that sold (among other things) Baby Clothes. That looks like this per company:

CompanySum of Sales
A30
B40

Again company C isn't shown because it didn't sell Baby Clothes so it's not returned by p({$<Category={'Baby Clothes'}>} Company). Instead of the just the sales of the Baby Clothes now the total sales per company is shown.

sum({$<Company = p({$<Category={'Baby Clothes'}>} Company) >-<Company = p({$<Category={'SportsWear'}>} Company) >} Sales) will return the total sales for each company that sold Baby Clothes, but also did not sell any SportsWear. The result per company looks like this:

CompanySum of Sales
B40

Company A sold Baby Clothes, but also SportWear so it's excluded from the sum. The result is that only the total sales of Company B is shown.

Finally e(): sum({$<Company = e({$<Category={'Baby Clothes'}>} Company) >} Sales) will return the total sales of any company that did not sell any Baby Clothes. With the sample data it will return this since only company C didn't sell any Baby Clothes:

CompanySum of Sales
C35

I hope this helps make things clear for you.


talk is cheap, supply exceeds demand
8 Replies

Re: Indirect Set Analysis

You can't leave out the p operator. This isn't correct syntax: {$<CategoryName={'Baby Clothes'}>}CompanyName. Nor would sum({$<CategoryName={'Baby Clothes'}>}CompanyName) be since names are not something to aggregate. It's not clear what you actually meant. I'll try to explain things a bit below.

Some sample data:

CompanyCategorySales
ABaby Clothes10
ASportsWear20
BBaby Clothes15
BDresses25
CSportsWear15
CDresses20

sum({$<Category={'Baby Clothes'}>}Sales) will return the sum of sales for only Baby Clothes. It will return this per company:

CompanySum of Sales
A10
B15

Company C didn't sell any Baby Clothes so it is not shown in the table.

sum({$<Company = p({$<Category={'Baby Clothes'}>} Company) >} Sales) will return the total sales for each company that sold (among other things) Baby Clothes. That looks like this per company:

CompanySum of Sales
A30
B40

Again company C isn't shown because it didn't sell Baby Clothes so it's not returned by p({$<Category={'Baby Clothes'}>} Company). Instead of the just the sales of the Baby Clothes now the total sales per company is shown.

sum({$<Company = p({$<Category={'Baby Clothes'}>} Company) >-<Company = p({$<Category={'SportsWear'}>} Company) >} Sales) will return the total sales for each company that sold Baby Clothes, but also did not sell any SportsWear. The result per company looks like this:

CompanySum of Sales
B40

Company A sold Baby Clothes, but also SportWear so it's excluded from the sum. The result is that only the total sales of Company B is shown.

Finally e(): sum({$<Company = e({$<Category={'Baby Clothes'}>} Company) >} Sales) will return the total sales of any company that did not sell any Baby Clothes. With the sample data it will return this since only company C didn't sell any Baby Clothes:

CompanySum of Sales
C35

I hope this helps make things clear for you.


talk is cheap, supply exceeds demand
marco_puccetti
Contributor

Re: Indirect Set Analysis

Thank you for your answer is very usefull!!

Marco

eugenyilyin
Contributor II

Re: Indirect Set Analysis

OK

How i can get this table?

CategorySalesComment
Baby Clothes70Sum Company=A,B
Dresses75Sum Company=B,C
SportsWear65Sum Company=A,C

Eugeny

marco_puccetti
Contributor

Re: Indirect Set Analysis

Although i think the multiple condition operator is allowed, a simple form could be the following:

sum({$<Company={'A'}>}Sales)+sum({$<Company={'B'}>}Sales)

sum({$<Company={'B'}>}Sales)+sum({$<Company={'C'}>}Sales)

sum({$<Company={'A'}>}Sales)+sum({$<Company={'C'}>}Sales)

Somebody knows other form?

Marco

eugenyilyin
Contributor II

Re: Indirect Set Analysis

No,

CategorySalesExpressions from text box
Baby Clothes70=sum({$<Company = p({$<Category={'Baby Clothes'}>} Company) >} Sales)
Dresses75=sum({$<Company = p({$<Category={'Dresses'}>} Company) >} Sales)
SportsWear65=sum({$<Company = p({$<Category={'SportsWear}>} Company) >} Sales)

I want to use one expression in the table.

=sum({$<Company = p({$<Category=...?...>} Company) >} Sales)

marco_puccetti
Contributor

Re: Indirect Set Analysis

If you want the sum per category of the number of 'Baby Clothes', try with this, i can't test it but i'm confident that it's working (you can then replicate this with the other categories):

sum({$<Category = {'Baby  Clothes'} Category) >} Sales)

Notice that 70 is not the sum of the only 'Baby Clothes' category in A and B company.

Let me know if it's working.

Marco

eugenyilyin
Contributor II

Re: Indirect Set Analysis

I want get total sales of outlets carrying category for ACV&PCV calculation.

marco_puccetti
Contributor

Re: Indirect Set Analysis

To get the total sale of a two particular categories you have to use this statement:

sum({$<Category = {'Baby  Clothes','Dresses'} >} Sales)

In this case you get the sum of Sales for baby Clothes and Dresses categories.

Marco

Community Browser