topic Re: sum to current date selection in QlikView

sum to current date selection

mogaka1234 — Tue, 23 May 2017 13:41:17 GMT

i am trying to add expenditure to current selection using: =sum({<[A/C Type]={'Direct Staff Costs*'}, FinResultsDate={'>=$(=date('01/09/2016','MMM-YY')) <=$(=date(max(FinResultsDate),'MMM-YY'))'}>} [USD amount])

My year starts in september. Anyhelp?

Re: sum to current date selection

Anil_Babu_Samineni — Tue, 23 May 2017 15:20:53 GMT

You mean to say this?

=sum({<expenditure=, [A/C Type]={'Direct Staff Costs*'}, FinResultsDate={'>=$(=date('01/09/2016','MMM-YY')) <=$(=date(max(FinResultsDate),'MMM-YY'))'}>} [USD amount])

Or Is there any error you are seeing?

Re: sum to current date selection

Digvijay_Singh — Tue, 23 May 2017 15:35:53 GMT

May be -

=sum({<[A/C Type]={'Direct Staff Costs*'},

FinResultsDate={"$(='>=' & date(makedate(2016,09,01),'MMM-YY')&'<='& date(max(FinResultsDate),'MMM-YY'))"}>} [USD amount])

Re: sum to current date selection

mogaka1234 — Tue, 23 May 2017 21:05:29 GMT

I am getting zeros:

FinResultDate	Cost GL	A/C Type	=sum({<[A/C Type]={'Direct Staff Costs*'}, FinResultsDate={">=Sep-16<=Mar-17"}>} [USD amount])
Apr 17	10.00	Direct Staff Costs	0
Mar 17	11.00	Direct Staff Costs	0
Feb 17	13.00	Direct Staff Costs	0
Jan 17	17.00	Direct Staff Costs	0
Dec 16	2,001.00	Direct Staff Costs	0
Nov 16	11.00	Direct Staff Costs	0
Oct 16	14.00	Direct Staff Costs	0
Sep 16	2.00	Direct Staff Costs	0

Re: sum to current date selection

Digvijay_Singh — Tue, 23 May 2017 22:21:45 GMT

What format FinResultsDate you have in your data?

Re: sum to current date selection

Digvijay_Singh — Tue, 23 May 2017 22:40:17 GMT

Try to avoid date specific formatting inside set expression as far as possible, check this as well -

=sum({<[A/C Type]={'Direct Staff Costs*'},

FinResultsDate={"$(='>=' &chr(39)& date(makedate(2016,09,01),'MMM-YY')&chr(39)&'<='& chr(39)&date(max(FinResultsDate),'MMM-YY'))&chr(39)"}>} [USD amount])

Re: sum to current date selection

mogaka1234 — Wed, 24 May 2017 07:59:54 GMT

Still getting 0.

Re: sum to current date selection

mogaka1234 — Wed, 24 May 2017 08:00:52 GMT

FinResultDate is in the format MMM-YY

Re: sum to current date selection

Kushal_Chawda — Wed, 24 May 2017 10:45:54 GMT

Make sure that FinResultsDate is in dual format, to check put =max(FinResultsDate) in text object if it returns number then it is in dual format. If so below expression should work

sum({<[A/C Type]={'Direct Staff Costs*'}, FinResultsDate={'>=$(=date(date#('01/09/2016','DD/MM/YYYY'),'MMM-YY'))<=$(=max(FinResultsDate))'}>} [USD amount])

Re: sum to current date selection

mogaka1234 — Wed, 24 May 2017 11:32:28 GMT

Thanks, This worked

Re: sum to current date selection

Kushal_Chawda — Wed, 24 May 2017 12:43:33 GMT

Please mark the answer as correct

Re: sum to current date selection

sonysree88 — Fri, 26 May 2017 10:10:41 GMT

Yes it will work correctly when you have the dual format