Sum Between Dates

[ndeeleysww]

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

[giacomom · Accepted solution]

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

[ndeeleysww]

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

[Gysbert_Wassenaar]

Like this:

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

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

[giacomom]

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

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

Cheers,

Giacomo

[ndeeleysww]

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

[ndeeleysww]

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

[giacomom]

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

[ndeeleysww]

Brilliant, thanks for your help - very much appreciated!

[giacomom]

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