Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Issue while calculating total in Dimensionality function

Hey friends,

Doc noClearing DocBilling DocDoc TypePlan_DateBLDATReceipt Amount<180 daysDays Difference
????
1700000608#-DZ01-03-2017-16364-16364
170000060817000006087447100532DZ01-03-201721-1-2017-18818118818139
170000061117000006111050012115DZ01-03-201731-12-2016-8170501739050160
170000061117000006291050012169DZ01-03-201724-01-20170.0022000036
170000061117000006291700000611DZ01-03-201714-03-2017780000--13
1700000629#-DZ01-03-2017-846000--
170000062917000006291050012169DZ01-03-201724-01-20170.0022000036
170000062917000006291700000611DZ01-03-201714-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

Thanks & Regards,

Girish Chhatani

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

I tried using Expression total and it worked!!!

Thank you,

Girish Chhatani

7 Replies
MVP

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

Anonymous
Not applicable
Author

Its showing blank.

MVP

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
Not applicable
Author

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

Anonymous
Not applicable
Author

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

MVP

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
Not applicable
Author

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

I tried using Expression total and it worked!!!

Thank you,

Girish Chhatani

Community Browser