Set analysis - calculate on one level - show on another

[#1]

Hi

I have made a set expression where I have chosen that the calculation should never go lower than a certain level in the product hierarchy.

I use this expression for KPI 2:

sum({$< SubCategory = , Segment = , Product =  >} total <Category, Country, CategoryGroup> Value)

The problem is that it shows all the products independent on whether they contribute to the total or not.

KPI 1 shows the which products that have value (sum(Value) )

How do I tell the chart only to show the products that have value and still get my Category limit and my totals?

I have tried with this:

sum({$< SubCategory = , Segment = , Product = , Value={">1"}  >}total <Category, Country, CategoryGroup> Value)

but it still shows to many Products. I think I have to tell the expression that Value={">1"} have to be calculated at Productlevel but how to I do that?

I appreciate all the help I can get ☺

René

[lironbaram]

the fast solution will be

if([KPI1]>0,sum({$< SubCategory = , Segment = , Product =  >} total <Category, Country, CategoryGroup> Value))

[#3]

Liron - Thanks for your suggestion – it is very good and will give a far better chart expression than mine.

But I’m still wondering if it could be done by “set” instead of “If”

Is It possible to make a set-of-products with value > 0?

I can put a , Value={>0} into my expression but I think it looks if Value > 0 on the total levels. So how do I get this to look at product level?

(sum({$ Value) ,0)

Fra: liron baram <mailto:[mailto:qliktech@sgaur.hosted.jivesoftware.com]>

Sendt: 14. februar 2012 14:55

Til: Hansen, René

Emne: - Re: Set analysis - calculate on one level - show on another

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Set analysis - calculate on one level - show on another

created by liron baram<http://community.qlik.com/people/roiuser> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/191449#191449

[lironbaram]

hi

as sum(Value) isnt a field to the best of my knowledge you cant use it in set analysis

in set analysis you make selection in fields that are diffrent from the global selections in the model

you can go Value>0 but what it will make is selecting only the records where the value>0

regardless of the total

why you have problem using if function

[#5]

Hi

No I don’t have a problem with IF in general but I still don’t have exactly the expression that I want.

If you look at the example I have 5 products in my chart. What I would like is that KPI 2 is only calculated for the products where value2>0 (shown by KPI3)

KPI 2 is defined as:

if(sum({$   CtSourced = ,  SubCategory = , Segment = , Product = , DataSource = >}Value2)+sum(Value1)>0,

sum({$ CtSourced = , SubCategory = , Segment = , Product = , DataSource = >} total <Category,Country, Brand, Supplier, CategoryGroup> Value2)

,0)”

So the challenge is the IF helps me pick the right products but I can’t control which of the product the calculation should be done on. In the perfect solution I would have the 5 products and KPI 2 where calculated only on the 2 with KPI3=x (product 4-5)

The difference between product 1-3 and 4-5 is that 1-3 have value1 transactions while 4-5 have value2 transactions.

And the difference between value 1 and value 2 is that none of the value2 transactions have attributes CtSourced and DataSource <> 0