Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
Company | Category | Sales |
---|---|---|
A | Baby Clothes | 10 |
A | SportsWear | 20 |
B | Baby Clothes | 15 |
B | Dresses | 25 |
C | SportsWear | 15 |
C | Dresses | 20 |
sum({$<Category={'Baby Clothes'}>}Sales) will return the sum of sales for only Baby Clothes. It will return this per company:
Company | Sum of Sales |
---|---|
A | 10 |
B | 15 |
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:
Company | Sum of Sales |
---|---|
A | 30 |
B | 40 |
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:
Company | Sum of Sales |
---|---|
B | 40 |
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:
Company | Sum of Sales |
---|---|
C | 35 |
I hope this helps make things clear for you.
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:
Company | Category | Sales |
---|---|---|
A | Baby Clothes | 10 |
A | SportsWear | 20 |
B | Baby Clothes | 15 |
B | Dresses | 25 |
C | SportsWear | 15 |
C | Dresses | 20 |
sum({$<Category={'Baby Clothes'}>}Sales) will return the sum of sales for only Baby Clothes. It will return this per company:
Company | Sum of Sales |
---|---|
A | 10 |
B | 15 |
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:
Company | Sum of Sales |
---|---|
A | 30 |
B | 40 |
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:
Company | Sum of Sales |
---|---|
B | 40 |
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:
Company | Sum of Sales |
---|---|
C | 35 |
I hope this helps make things clear for you.
Thank you for your answer is very usefull!!
Marco
OK
How i can get this table?
Category | Sales | Comment |
---|---|---|
Baby Clothes | 70 | Sum Company=A,B |
Dresses | 75 | Sum Company=B,C |
SportsWear | 65 | Sum Company=A,C |
Eugeny
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
No,
Category | Sales | Expressions from text box |
---|---|---|
Baby Clothes | 70 | =sum({$<Company = p({$<Category={'Baby Clothes'}>} Company) >} Sales) |
Dresses | 75 | =sum({$<Company = p({$<Category={'Dresses'}>} Company) >} Sales) |
SportsWear | 65 | =sum({$<Company = p({$<Category={'SportsWear}>} Company) >} Sales) |
I want to use one expression in the table.
=sum({$<Company = p({$<Category=...?...>} Company) >} Sales)
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
I want get total sales of outlets carrying category for ACV&PCV calculation.
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