Issue while calculating total in Dimensionality function

[Anonymous]

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

[sunny_talwar]

What does it show today for the total? Does it show anything?

[Anonymous]

Its showing blank.

[sunny_talwar]

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?

[Anonymous]

If i use Sum outside of outer if block then total is calculated but result is incorrect. i.e sum(if dimensionality()=0 .....  )

[Anonymous]

But my expression number for each document no is coming correct and validated the same but total is going incorrect.

[sunny_talwar]

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?

[Anonymous · Accepted solution]

ya true you are correct... Thank you very much for your help...

I tried using Expression total and it worked!!!

Thank you,

Girish Chhatani