- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Issue while calculating total in Dimensionality function
Hey friends,
Doc no | Clearing Doc | Billing Doc | Doc Type | Plan_Date | BLDAT | Receipt Amount | <180 days | Days Difference |
---|---|---|---|---|---|---|---|---|
???? | ||||||||
1700000608 | # | - | DZ | 01-03-2017 | - | 16364 | -16364 | |
1700000608 | 1700000608 | 7447100532 | DZ | 01-03-2017 | 21-1-2017 | -188181 | 188181 | 39 |
1700000611 | 1700000611 | 1050012115 | DZ | 01-03-2017 | 31-12-2016 | -8170501 | 7390501 | 60 |
1700000611 | 1700000629 | 1050012169 | DZ | 01-03-2017 | 24-01-2017 | 0.00 | 220000 | 36 |
1700000611 | 1700000629 | 1700000611 | DZ | 01-03-2017 | 14-03-2017 | 780000 | - | -13 |
1700000629 | # | - | DZ | 01-03-2017 | - | 846000 | - | - |
1700000629 | 1700000629 | 1050012169 | DZ | 01-03-2017 | 24-01-2017 | 0.00 | 220000 | 36 |
1700000629 | 1700000629 | 1700000611 | DZ | 01-03-2017 | 14-03-2017 | -1846000 | - | -13 |
Total is coming wrong.
For calculating total i used expression as mentioned below:-
if(Dimensionality()=0 and
(
date(Plan_Date,'DD-MM-YYYY')-date(BLDAT,'DD-MM-YYYY')>0
and date(Plan_Date,'DD-MM-YYYY')-date(BLDAT,'DD-MM-YYYY')<=180
)
and match([Doc Type],'DZ','BC')
and [Doc no]<>[Billing Doc]
,sum(BSE_CLR_Amount-BSE_CLR_Diff_Amt)
,if(
match([Doc Type],'DZ','BC')
and [Doc no]<>[Billing Doc]
and [Reciept Amount]>0
,sum([Reciept Amount])*-1
)
Total should come = 8,002,318.00
Can anyone please help me while calculating the total.
Thanks & Regards,
Girish Chhatani
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ya true you are correct... Thank you very much for your help...
I tried using Expression total and it worked!!!
Thank you,
Girish Chhatani
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What does it show today for the total? Does it show anything?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Its showing blank.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Seems like an issue with your if statement.... Specifically here:
if(Dimensionality()=0 and
(
date(Plan_Date,'DD-MM-YYYY')-date(BLDAT,'DD-MM-YYYY')>0
and date(Plan_Date,'DD-MM-YYYY')-date(BLDAT,'DD-MM-YYYY')<=180
)
and match([Doc Type],'DZ','BC')
and [Doc no]<>[Billing Doc]
,sum(BSE_CLR_Amount-BSE_CLR_Diff_Amt)
Total rows doesn't have a single Plan_Date, unless all the rows have the same information. and similarly for the other fields. Do you really need to check these for the total rows?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If i use Sum outside of outer if block then total is calculated but result is incorrect. i.e sum(if dimensionality()=0 ..... )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
But my expression number for each document no is coming correct and validated the same but total is going incorrect.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Well Dimensionality() = 0 is already the total row.... from what I am reading... I think you are trying to use this for total row sum(BSE_CLR_Amount-BSE_CLR_Diff_Amt) and something else for other rows, right?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ya true you are correct... Thank you very much for your help...
I tried using Expression total and it worked!!!
Thank you,
Girish Chhatani