topic Re: Conditional Dimension or Expression in QlikView

Conditional Dimension or Expression

Wed, 05 Mar 2014 15:54:10 GMT

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

Re: Conditional Dimension or Expression

Anonymous — Wed, 05 Mar 2014 16:15:26 GMT

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

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

Re: Conditional Dimension or Expression

Wed, 05 Mar 2014 16:27:54 GMT

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

Re: Conditional Dimension or Expression

Anonymous — Wed, 05 Mar 2014 16:54:52 GMT

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])))

Re: Conditional Dimension or Expression

Wed, 05 Mar 2014 16:59:25 GMT

Hey Shoaib,

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

Product is a Dim and the rest are expressions?

Thanks

Re: Conditional Dimension or Expression

Wed, 05 Mar 2014 17:01:40 GMT

Product is dimesion and the rest expression labels

Re: Conditional Dimension or Expression

Anonymous — Wed, 05 Mar 2014 17:05:08 GMT

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

Re: Conditional Dimension or Expression

Anonymous — Wed, 05 Mar 2014 17:07:53 GMT

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)

Re: Re: Conditional Dimension or Expression

Wed, 05 Mar 2014 17:11:11 GMT

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

Thanks

Re: Re: Conditional Dimension or Expression

Wed, 05 Mar 2014 17:11:52 GMT

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)

Re: Re: Conditional Dimension or Expression

Wed, 05 Mar 2014 17:12:10 GMT

Ajay thanks but i can't download as I am restricted and using personal edition

Re: Conditional Dimension or Expression

Wed, 05 Mar 2014 17:17:06 GMT

You can use this expression.

If(Product='B' and Stage2=2,0,Stage2)

Where Stage2 is an expression label.

Re: Conditional Dimension or Expression

Anonymous — Wed, 05 Mar 2014 17:23:24 GMT

As I understand you have an expression in Follow Up Docs and Returned Docs, that you want to have it as 0 if product=B, so why don't you keep your expression as it's and multiply it by *only(if(Product='B',0,1))

Re: Conditional Dimension or Expression

Anonymous — Wed, 05 Mar 2014 17:36:23 GMT