Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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?
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)
Post a screenshot of the data model. Or better yet a small qlikview document with some example data that demonstrates the problem.
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!
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