topic Re: Remove dash (-) in QlikView

Remove dash (-)

Mon, 03 Mar 2014 17:01:45 GMT

How can I remove - in a straight table as attached because it is not giving me total. I am using the following in expression. e.g for CJA Tray = 'NAPS CJA Automatic Check'

sum({<Tray={'NAPS CJA Automatic Check'}>}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 CJA Automatic Check'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

Re: Remove dash (-)

alexandros17 — Mon, 03 Mar 2014 17:04:50 GMT

Set in tab presentation

Null Values = 0

Missing Values = 0

Re: Remove dash (-)

Mon, 03 Mar 2014 17:20:05 GMT

Now in total it shows 0 instead of -

Re: Remove dash (-)

antoniotiman — Mon, 03 Mar 2014 17:25:27 GMT

You can use Dimensionality() (see Help).

Re: Remove dash (-)

stevedark — Mon, 03 Mar 2014 22:20:58 GMT

Hi there,

I think the problem you may be having is that in your expression you only have a Sum around the first part. The rest of the expression has no aggregation around it - so it will work on a single line (as long as the dimensions make those values unique) but in a total where you will have more than one possible this will return null and wipe out the rest of the expression.

If you put a sum around each of the if statements (or a max or min) then you should get a result in the total.

The other thing you can do is change the Total Mode on the expression to be Sum Of Rows rather than Expression Total. You need to ensure you understand what the difference is though - as you may get results different to what you expect.

Hope that helps,

Steve

Re: Remove dash (-)

Tue, 04 Mar 2014 09:19:58 GMT

Hi Steve,

Thanks. i tries the following by adding sum before if i.e. Sum(if. but it changed all CJA numbers into 0. Also I wrote Sum Of Rows in Total expresssion and all numbers changed into zero

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

sum(if(frac(date(pre))<num('$(vQuitTime)')),sum(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) )

sum( if(frac(date(CMP_Date))>num('$(vStartTime)')),sum(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 CJA Automatic Check'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

Re: Remove dash (-)

stevedark — Tue, 04 Mar 2014 11:13:42 GMT

Sorry, I hadn't noticed you have sum statements inside your if as well. Surprised that the Sub Of Rows isn't working for you.

Given the sum statements inside the IF statements you will need to use an AGGR statement, to work out the value for each dimension and then tot it up. I would recommend finding more information on AGGR online, but this should give you a head start.

Wrap your initial statement in an aggregated sum, like this:

=sum(aggr(your-statement-here, your-1st-dimension, your-2nd-dimension))

The dimension fields in the above statement should list all of the dimensions that you have in your table.

Hope that helps.

Steve

Re: Remove dash (-)

Tue, 04 Mar 2014 11:28:27 GMT

Thanks but it did not work . I am using two fields "Pre" i.e. First Timestamp and "CMP_Date" i.e. Completion timestamp

Re: Remove dash (-)

stevedark — Tue, 04 Mar 2014 21:13:45 GMT

What you have there is a Pivot table rather than a Straight Table, so things are a bit different. I still think the answer may be with an Aggr. The Aggr parameter my just be the date, eg:

=sum(aggr(

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

, CMP_Date))

This means it will calculate a value for each date and then tot up the results afterwards. You may need to follow CMP_Date with Pre and ProductLow as well (comma separated). Experiment and see what you get.

Re: Remove dash (-)

Wed, 05 Mar 2014 09:32:17 GMT

Thanks Steve but it did not work. I have an other question please. 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(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)')