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'
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.
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
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:
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:
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.
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)')
