topic Re: How to calculate the total value within certain date range in QlikView

How to calculate the total value within certain date range

Wed, 25 Nov 2020 16:16:04 GMT

SelectedDate	WeekStartDayofSelectedDate	WeekEndDayofSelectedDate	Amount
1/20/2016	1/18/2016	1/24/2016	100
1/21/2016	1/18/2016	1/24/2016	200
1/22/2016	1/18/2016	1/24/2016	50
1/26/2016	1/25/2016	1/31/2016	150
1/27/2016	1/25/2016	1/31/2016	220

Hello ,

I want to calculate the total value within the date range of above selected date, but when i use both of following forluma, none of them are working, please help on this question.

Expecting result:

Total amount of week 1/18/2016~1/24/2016 = 100+200+50=350

Total amount of week 1/25/2016~1/31/2016 = 150+220=370

My forluma:

=If (SelectedDate>=WeekstartDayofSelectedDate and SelectedDate<=WeekEndDayofSelectedDate, sum(Amount))

=sum({<SelectedDate>='WeekstartDayofSelectedDate', SelectedDate<=WeekEndDayofSelectedDate>} Amount)

Thanks

Jessica

Re: How to calculate the total value within certain date range

marcus_sommer — Wed, 01 Jun 2016 09:16:39 GMT

It's not quite clear what you are want to do but if you simple used week as dimension (should be the same like your weekstart and weekend - if you need these representation you could concat both values - at best generated within a master-calendar, see also: How to use - Master-Calendar and Date-Values) and a simply expression like: sum(Amount) it should work.

- Marcus

Re: How to calculate the total value within certain date range

Wed, 01 Jun 2016 10:01:07 GMT

Hi ,

Like this