Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
girishchhatani1
Contributor III
Contributor III

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
girishchhatani1
Contributor III
Contributor III
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?

girishchhatani1
Contributor III
Contributor III
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?

girishchhatani1
Contributor III
Contributor III
Author

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

girishchhatani1
Contributor III
Contributor III
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?

girishchhatani1
Contributor III
Contributor III
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