topic Re: Sum between two dates in QlikView

Sum between two dates

samvile18 — Tue, 13 Mar 2012 11:20:44 GMT

Good morning all,

Can someone please help me with some syntax?

I have two fields named StartDate and EndDate and I need to sum the total amount of Value that falls between them.

I'm getting a little stuck with the synta, any help is greatly appreciated.

Re: Sum between two dates

jvitantonio — Tue, 13 Mar 2012 11:24:44 GMT

Correcting

sum( {$<StartDate = {“>10/02/2012”}, EndDate = {“<10/15/2012”} >} Sales )

Sum between two dates

samvile18 — Tue, 13 Mar 2012 11:29:03 GMT

Thanks for that...I'm assuming I can use the min and max functions instead of stating an actual date?

Sum between two dates

jvitantonio — Tue, 13 Mar 2012 11:32:00 GMT

Of course.You can adapt it to your situation. If you have any further problemas, post your app. so we work on it. Good luck!

Sum between two dates

CELAMBARASAN — Tue, 13 Mar 2012 11:44:22 GMT

Hi,

Try with this if your comparing to another field based on the start and end date fields.

Sum( {$<DateField = {“>=$(=Min(StartDate))<=$(=Max(EndDate))”} >} Sales )

Comparing within the same fields means

Sum( {$<StartDate= {“>=$(=Min(StartDate))”}, EndDate= {“<=$(=Max(EndDate))”} >} Sales )

Celambarasan

Re: Sum between two dates

samvile18 — Tue, 13 Mar 2012 13:38:00 GMT

This is what I have so far and it seems to be working...

...looking at it actually it seems to be pretty much exactly the same as Celambarasan's above!!

Sum({<ExtractDate={">=$(=Min(CampaignStartDate))<=$(Max(CampaignEndDate))"}>}Value)

Re: Sum between two dates

CELAMBARASAN — Tue, 13 Mar 2012 13:41:45 GMT

Hi,

If StartDate and EndDate field also linked with the table then exclude the selection of it.

Check with this

Sum({<ExtractDate={">=$(=Min(CampaignStartDate))<=$(=Max(CampaignEndDate))"},

CampaignStartDate=,CampaignEndDate=>}Value)

Hope it helps

Celambarasan

Re: Sum between two dates

Anonymous — Tue, 03 Jan 2017 17:45:49 GMT

how do you do it if u want the date to be dynamic. for example you want the data from today to 50 days prior.

when u check tomorrow it should show values 50 days prior to the respective day.

can u pls help me out on this

Re: Sum between two dates

Ahmed_Hisham — Tue, 28 Sep 2021 11:52:25 GMT

Hi I am using the below equation

=sum({$ <[Month Date.autoCalendar.Date]={">=1/1/2021<=31/7/2021"}>} [Qty])

It only returns values for 31/7/2021

Any advice