Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mogaka1234
New Contributor

sum to current date selection

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?

11 Replies

Re: sum to current date selection

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?

Life is so rich, and we need to respect to the life !!!
Digvijay_Singh
Honored Contributor III

Re: sum to current date selection

May be -

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

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

mogaka1234
New Contributor

Re: sum to current date selection

I am getting zeros:

 

FinResultDateCost GLA/C Type=sum({<[A/C Type]={'Direct Staff Costs*'}, FinResultsDate={">=Sep-16<=Mar-17"}>} [USD amount])
Apr 1710.00Direct Staff Costs0
Mar 1711.00Direct Staff Costs0
Feb 1713.00Direct Staff Costs0
Jan 1717.00Direct Staff Costs0
Dec 162,001.00Direct Staff Costs0
Nov 1611.00Direct Staff Costs0
Oct 1614.00Direct Staff Costs0
Sep 162.00Direct Staff Costs0
Digvijay_Singh
Honored Contributor III

Re: sum to current date selection

What format FinResultsDate you have in your data?

Digvijay_Singh
Honored Contributor III

Re: sum to current date selection

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

mogaka1234
New Contributor

Re: sum to current date selection

Still getting 0.

AAAA.PNG

mogaka1234
New Contributor

Re: sum to current date selection

FinResultDate is in the format MMM-YY

Re: sum to current date selection

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

mogaka1234
New Contributor

Re: sum to current date selection

Thanks, This worked

Community Browser