topic Re: Issue while calculating total in Dimensionality function in QlikView

Issue while calculating total in Dimensionality function

Anonymous — Mon, 05 Jun 2017 10:55:59 GMT

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

Re: Issue while calculating total in Dimensionality function

sunny_talwar — Mon, 05 Jun 2017 10:57:49 GMT

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

Re: Issue while calculating total in Dimensionality function

Anonymous — Mon, 05 Jun 2017 10:59:39 GMT

Its showing blank.

Re: Issue while calculating total in Dimensionality function

sunny_talwar — Mon, 05 Jun 2017 11:02:26 GMT

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?

Re: Issue while calculating total in Dimensionality function

Anonymous — Mon, 05 Jun 2017 11:05:25 GMT

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

Re: Issue while calculating total in Dimensionality function

Anonymous — Mon, 05 Jun 2017 11:06:42 GMT

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

sunny_talwar — Mon, 05 Jun 2017 11:07:23 GMT

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?

Re: Issue while calculating total in Dimensionality function

Anonymous — Mon, 05 Jun 2017 11:44:00 GMT

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

I tried using Expression total and it worked!!!

Thank you,

Girish Chhatani