Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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

Re: Issue while calculating total in Dimensionality function

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

Re: Issue while calculating total in Dimensionality function

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

girishchhatani1
New Contributor III

Re: Issue while calculating total in Dimensionality function

Its showing blank.

Re: Issue while calculating total in Dimensionality function

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

Re: Issue while calculating total in Dimensionality function

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

girishchhatani1
New Contributor III

Re: Issue while calculating total in Dimensionality function

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

Re: Issue while calculating total in Dimensionality function

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

Re: Issue while calculating total in Dimensionality function

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

I tried using Expression total and it worked!!!

Thank you,

Girish Chhatani