topic Re: Restrict Calculation based on Dimension in QlikView

Restrict Calculation based on Dimension

Wed, 05 Mar 2014 09:40:17 GMT

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({<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)')

Re: Restrict Calculation based on Dimension

Wed, 05 Mar 2014 09:56:27 GMT

If I'm not mistaken, you can have that conditional in your dimension and your expression will inherit that condition upon calculation. (can't remember where I tested this, but give it a try and let me know )

Dimension: if(match(FollowUp,'Loan','MasterPlan')<>0 or match(ReturnedDoc,'Loan','MasterPlan)<>0,Application,null())

Tick supress nulls

Your expression should now exclude based on your dimension rules.

Another approach would to have another IF statement encapsulate your current expression with the condition... So If(match(FollowUp,'Loan','MasterPlan')<>0 or match(ReturnedDoc,'Loan','MasterPlan)<>0,original expression)

Hope you come right

Cheers,

Byron

Re: Restrict Calculation based on Dimension

Wed, 05 Mar 2014 10:11:50 GMT

Thanks Byron. Could you please tell where should I add the following under Add Calculated Dimension?
if(match(FollowUp,'Loan','MasterPlan')<>0 or match(ReturnedDoc,'Loan','MasterPlan)<>0,Application,null())

I tried If(match(FollowUp,'Loan','MasterPlan')<>0 or match(ReturnedDoc,'Loan','MasterPlan)<>0,original expression) but it gives error

Re: Restrict Calculation based on Dimension

Wed, 05 Mar 2014 10:15:20 GMT

Yes add it under calculated dimension.

When you tried the next approach in the expression and you say it gave an error. What error? Did the expression syntax check say 'expression ok'?

Cheers,

Byron

Re: Restrict Calculation based on Dimension

Wed, 05 Mar 2014 10:36:21 GMT

I added

=if(match("Follow Up Docs",'Loan','Masterplan')<>0 or match("Returned Docs",'Loan','MasterPlan')<>0,Application_Id,null())

under Add Calculated Dimensions but did not work. Follow Up Docs and Returned Docs are expression labels. In the data they are called NAPS Follow UP documents and NAPS Returned Documents. I tried both but did not work.

I used the following in expression but says Error in expression:

If(match("Follow Up Docs",'Loan','MasterPlan')<>0 or match("Returned Docs",'Loan','MasterPlan)<>0,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)')

Re: Restrict Calculation based on Dimension

Thu, 06 Mar 2014 06:54:00 GMT

Hi Shoaib,

Will it be possible for you to share the application or a sample application with the same scenario to understand the requirement better.

Vino

Re: Restrict Calculation based on Dimension

Thu, 06 Mar 2014 09:04:11 GMT

Hi Vinoth,

I have solved it. Thanks