
Set analysis  calculate on one level  show on another
Liron Baram Feb 14, 2012 8:54 AM (in response to RLambert)the fast solution will be
if([KPI1]>0,sum({$< SubCategory = , Segment = , Product = >} total <Category, Country, CategoryGroup> Value))

Re: Set analysis  calculate on one level  show on another
RLambert Feb 15, 2012 7:07 AM (in response to Liron Baram )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 setofproducts 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 qliktech@sgaur.hosted.jivesoftware.com<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

Set analysis  calculate on one level  show on another
Liron Baram Feb 15, 2012 7:33 AM (in response to RLambert)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

Re: Set analysis  calculate on one level  show on another
RLambert Feb 17, 2012 2:53 AM (in response to Liron Baram )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 45)
The difference between product 13 and 45 is that 13 have value1 transactions while 45 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

Attachment.docx 19.4 K



