Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
LoKi_asterix
Contributor III
Contributor III

YTD expressions responding to month selections

Hi everyone,

I have two expressions that work well for calculating Year-to-Date totals, but I'm struggling to make them respond properly when I select specific months.

Sum({$<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(Max(Date), 'DD/MM/YYYY'))"},Year,Quarter,Month,Week>} Sales)

Sum({$<Date={">=$(=YearStart(Max(Date), -1))<=$(=AddYears(Max(Date), -1))"},Year,Quarter,Month,Week>} Sales)

Right now, these always show the full year accumulation (Jan through the max selected date). What I'd like is:

When no months are selected: Keep the current YTD behavior

When specific months are selected (e.g., Aug & Sept): Show the sum for just those selected months (Aug + Sept), not the cumulative total from January

Basically, I want the expressions to be show cumulative when nothing is selected, but show the actual sum of selected months when I make a month selection.

Has anyone solved this before? I'd really appreciate any guidance on how to modify these expressions to handle both scenarios.

Thanks in advance!

1 Solution

Accepted Solutions
rubenmarin

Hi, if you do a "Date(YearStart(Max(Date))", it will filter from the start of the year, you can filter from the month start using: Date(MonthStart(Min(Date))

Note that this will sum consecutive months, if you select april and june (somewhat rare selection), it will add may data.to void this there could be differnt solutions based on the laoded data, a common solution may be checking if ther is a month selected and use differnt expression based on that, like:

If(GetSelectedCount(MonthField), Sum(Sales), [CurrentExpressionForYTD])

View solution in original post

3 Replies
MatheusC
Specialist II
Specialist II

If you need your measure to respond to your selection, you can't ignore this filter in your expression:

Could you do it with just a max(Year)?

If you don't have one, you can also try creating it from your Date field.

Year(FieldDate) as FieldYear
or
Year(Date(FieldDate)) as FieldYear
or
Year(Date#(FieldDate,'DD/MM/YYYY')) as FieldYear

So with a simple Max(Year), it should work.

sum({<FieldYear = {"$(=Max(FieldYear))"}>} Sales)


- Regards

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
rubenmarin

Hi, if you do a "Date(YearStart(Max(Date))", it will filter from the start of the year, you can filter from the month start using: Date(MonthStart(Min(Date))

Note that this will sum consecutive months, if you select april and june (somewhat rare selection), it will add may data.to void this there could be differnt solutions based on the laoded data, a common solution may be checking if ther is a month selected and use differnt expression based on that, like:

If(GetSelectedCount(MonthField), Sum(Sales), [CurrentExpressionForYTD])

LoKi_asterix
Contributor III
Contributor III
Author

I made a slight adjustment to the expression, but it works as expected now. Thanks!