6 Replies Latest reply: Dec 15, 2016 11:43 AM by Sunny Talwar

# Sum between first date and max selection date

Hi there.

I'm quite new to Qlik, and I've tried to search for the answer, but with no success.

I was hoping you could help me out on this scenario.

I'm working on an app for inventory balances, which pretty much means I have to sum everything from first date to "max of selection date" if you know what I mean.

I'm creating a line chart, but all that does at the moment is summing the transactions within the date selections I make. That's obvious and I get that. But how would I go about accumulating the values of the selections I make?

The goal is a line chart showing the inventory balance by year, month and day (as a drilldown).

I have a MasterCalendar, and I made a field min(Date) as FirstDate resident MasterCalendar.

I was thinking the only way I'd be able to do this is through setting a daterange from FirstDate to.. max selected date, but I'm struggling.

This just sums the selection range.

=sum({\$ <Date={">=\$(=FirstDate)<=\$(max(Date))"}>}Amount)

=sum({\$ <Date={">=\$(=FirstDate)<=\$(=LastDate)"}>}Amount)

=sum({\$ <Date={">=1(=FirstDate)<=\$(=LastDate)"}>}Amount)

I'm new, please bear with me

Kind regards,

Vegard

• ###### Re: Sum between first date and max selection date

Normally this would suffice if the date format of the field Date is MM/DD/YYYY and your year, month and day fields are called Year, Month and Day:

=sum({\$<Year=,Month=,Day=,Date={"<=\$(=Date(max(Date),'MM/DD/YYYY'))"}>}Amount)

• ###### Re: Sum between first date and max selection date

Hello, Gysbert.

I tried with your formula, but it ended up being zero.

This is a preview of the mastercalendar:

And I changed to MM.DD.YYYY.

=sum({\$<Year=,Month=,Day=,Date={"<=\$(=Date(max(Date),'MM.DD.YYYY'))"}>}Amount)

Any tips?

• ###### Re: Sum between first date and max selection date

I think you might have DD.MM.YYYY instead of MM.DD.YYYY....

Try this:

=Sum({\$<Year=,Month=,Day=,Date={"<=\$(=Date(max(Date),'DD.MM.YYYY'))"}>}Amount)

• ###### Re: Sum between first date and max selection date

Ah, my god - I was sure I did use DD.MM.YYYY..

It's now summing properly

Really awesome, thanks!

I'm not sure how to reward in this case, you pointed out my baby-step error Sunny, but Gysbert came with the formula.

Thanks!

• ###### Re: Sum between first date and max selection date

As long as you get what you want, I or Gysbert don't really care who get the correct response. Beside, you already awarded me with the points via Helpful response.

Best,

Sunny

• ###### Re: Sum between first date and max selection date

Post a screenshot of the data model. Or better yet a small qlikview document with some example data that demonstrates the problem.