Conditional Dimension or Expression

[#1]

Hi,

I need to show Product B & Stage 2 value 0 instead of 2. How should I do it in dimension or expression please.

Product	Stage 1	Stage 2	Stage 3	Total
A	5	2	4	11
B	1	2	3	6
C	7	8	6	21

[Anonymous]

in expression instead of sum([Stage 2]), add an if statement...something like:

if(Product='B',0,sum([Stage 2]))

[#3]

Thanks but it did not work. I want to show zero only if Product = B and Stage 2. The rest should as is.

[Anonymous]

Hi,

As I understand you want to put zero instead of any value, when you have product=B, so you could use this expression

=sum(if(Product='B',0,sum([Stage 2])))

[#5]

Hey Shoaib,

Is Product, Stage1,Stage2,Stage3 all fields in your data model or

Product is a Dim and the rest are expressions?

Thanks

AJ

[#6]

Product is dimesion and the rest expression labels

[Anonymous]

From where do you get the value of Stage 2 if product is different than B

[Anonymous]

so Stage 2 is actually a label for the expression. that's why, the expression i pasted as is will not work for you. use the actual expression instead of sum([Stage 2]) in if statement i pasted.

so this should work -

if(Product='B',0, actual expression for Stage 2 here)

[#9]

Attached with an example of how to do it. There are two tables one is normal and the other is with your requirement.

Thanks

AJ

[#10]

Basically the real example is as below:

An application can have two products. e.g. Loan and Overdraft. The Follow Up Docs and Returned Docs should be blank in case of Overdraft but because of an application has loan and overdraft then the code calculates same time of loan for overdraft as attached. Can I restrict the code to only calculate if it is Loan or Masterplan under Follow Up Docs and Returned Docs.

sum({<Tray={'NAPS Follow Up Docs'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

+

if(frac(date(pre))<num('$(vQuitTime)'),if(frac(date(pre))>num('$(vStartTime)'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(pre,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(pre),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

+

if(frac(date(CMP_Date))>num('$(vStartTime)'),if(frac(date(CMP_Date))<num('$(vQuitTime)'),(Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))*24)/count(distinct Application_Id)