topic Re: Sum between first date and max selection date in QlikView

Sum between first date and max selection date

Anonymous — Tue, 13 Dec 2016 09:05:08 GMT

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.

I've been trying various approaches, but could you please advise?

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

Gysbert_Wassenaar — Tue, 13 Dec 2016 11:13:17 GMT

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

Anonymous — Tue, 13 Dec 2016 12:53:18 GMT

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

sunny_talwar — Tue, 13 Dec 2016 16:27:08 GMT

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

Gysbert_Wassenaar — Tue, 13 Dec 2016 16:32:47 GMT

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

Re: Sum between first date and max selection date

Anonymous — Thu, 15 Dec 2016 07:47:43 GMT

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

sunny_talwar — Thu, 15 Dec 2016 16:43:33 GMT

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