topic Re: Sum Between Dates in QlikView

Sum Between Dates

ndeeleysww — Fri, 28 Nov 2014 09:58:48 GMT

Hi,

I know this has been asked before but I cannot get any of the answers to work!

I'm using QlikSense and need to sum the values of a column TimeTaken where the column CompletionDate is between two dates.

I'm using this expression:

sum({<CompletionDate={">='01/04/2014'<='30/04/2014'"}>} [AmountCharged])

as a test, so that eventually I can replace the hard coded values with variables I have created in the load script.

However, it's not working. If someone can tell me why it would stop me from pulling my hair out! (what little there is of it).

Thanks

Neil

Update!

Okay, first issue resolved - I hadn't removed the timestamp from my CompletionDate, When I did that using this:

date(CompletionDate, 'DD/MM/YYYY') as CompletionDate

and then altered my expression to this:

sum({<CompletionDate={">=01/04/2014<=30/04/2014"}>} [AmountCharged])

It worked. I now need to replace the hard coded values with variables, if anyone can help?

Message was edited by: Neil Deeley

Re: Sum Between Dates

giacomom — Fri, 28 Nov 2014 10:23:04 GMT

Hi Neil,

I think you forgot the $ sign and you put extra ' surrounding the dates.

Try this:

=sum({$ <CompletionDate={">=01/04/2014<=30/04/2014"}>} [AmountCharged])

Regards,

Giacomo

Re: Sum Between Dates

ndeeleysww — Fri, 28 Nov 2014 10:28:10 GMT

Cheers Giacomo! I was (slowly) getting there! If I want to add a variable rather than a hard coded date, do I just swap it out like this:

=sum({$ <CompletionDate={">=StartDate<=EndDate"}>} [AmountCharged])

cheers

Neil

Re: Sum Between Dates

Gysbert_Wassenaar — Fri, 28 Nov 2014 10:41:36 GMT

Like this:

sum({<CompletionDate={">=$(vStartDate)<=$(vEndDate)"}>} [AmountCharged])

The variables should have values in the same date format as CompletionDate or just numeric values.

Re: Sum Between Dates

giacomom — Fri, 28 Nov 2014 10:41:50 GMT

In that case you have to use the dollar-sign expansion like this:

=sum({$ <CompletionDate={">=$(#StartDate)<=$(#EndDate)"}>} [AmountCharged])

Cheers,

Giacomo

Re: Sum Between Dates

ndeeleysww — Fri, 28 Nov 2014 10:55:05 GMT

Thanks both. It didn't work at first, but then I changed my SET variable to LET and it worked perfectly.

Re: Sum Between Dates

ndeeleysww — Fri, 28 Nov 2014 11:25:38 GMT

Hi Giacomo,

Just one further question.... I want to add a further condition, where ChargeableYN=1. Where do I put this in the expression? I can add 'and' statements to text field conditions using *, but it doesn't appear to work here.

Cheers

Neil

Re: Sum Between Dates

giacomom — Fri, 28 Nov 2014 11:31:45 GMT

Hi Neil,

you can list additional conditions inside the < > brackets separating them by the comma sign ',' like this:

=sum({$ <ChargeableYN={1}, CompletionDate={">=$(#StartDate)<=$(#EndDate)"}>} [AmountCharged])

Giacomo

Re: Sum Between Dates

ndeeleysww — Fri, 28 Nov 2014 11:35:50 GMT

Brilliant, thanks for your help - very much appreciated!

Re: Sum Between Dates

giacomom — Fri, 28 Nov 2014 11:46:23 GMT

You're welcome! I'm glad I could help.

If you want to know more about Set Analysis in more depth you can find useful information in the Reference Manual here https://drive.google.com/file/d/0BxOg0amRzk9vTDVpVzFYaUlGVFE/edit?pli=1 (page 807).

Cheers,

Giacomo