topic Re: Indirect Set Analysis in QlikView

Indirect Set Analysis

marco_puccetti — Fri, 07 Jun 2013 17:11:57 GMT

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

Re: Indirect Set Analysis

Gysbert_Wassenaar — Fri, 07 Jun 2013 18:29:48 GMT

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.

Re: Indirect Set Analysis

marco_puccetti — Sat, 08 Jun 2013 06:46:10 GMT

Thank you for your answer is very usefull!!

Marco

Re: Indirect Set Analysis

Eugeny_Ilyin — Sun, 11 Aug 2013 19:20:56 GMT

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

Re: Indirect Set Analysis

marco_puccetti — Mon, 12 Aug 2013 07:36:30 GMT

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

Re: Indirect Set Analysis

Eugeny_Ilyin — Mon, 12 Aug 2013 10:04:36 GMT

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)

Re: Indirect Set Analysis

marco_puccetti — Mon, 12 Aug 2013 13:46:23 GMT

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

Re: Indirect Set Analysis

Eugeny_Ilyin — Mon, 12 Aug 2013 14:56:48 GMT

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

Re: Indirect Set Analysis

marco_puccetti — Tue, 20 Aug 2013 15:32:20 GMT

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