Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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

Can anyone please help me while calculating the total.

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

View solution in original post

7 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

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
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.

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
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