Set Analysis -YTD Through Prior Full Month

[Qlik_UserEM]

Hello,

I am trying to created a dynamic YTD sales calculation through the prior full month based on a Year-Month selection.

I currently have this formula, however, when I select 2021-May it is still showing the sales for May as well:

SUM({<Year={'$(vMaxYear)'},%DATE_KEY={'<$(vMaxMonthStart)'},Month=,%DATE_KEY=,[Year-Month]=>}Sales)

%DATE_KEY = sales order date

vMaxYear = current year (2021)

vMaxMonthStart = start of the max month (5/1/2021)

What am I doing wrong?

[JuanGerardo · Accepted solution]

Hi @Qlik_UserEM, I am seeing a couple of things to check:

• When comparing and not filtering an exact value, you must use double quotes.
• Field %DATE_KEY is used twice in the filtering with different criteria

You can try:

SUM({<Year={'$(vMaxYear)'},%DATE_KEY={"<$(vMaxMonthStart)"},Month=,[Year-Month]=>}Sales)

And sometimes the date format can make your filter not work, so you can also try Date(vMaxMonthStart, 'MM/DD/YYYY') (use your default date format) instead of vMaxMonthStart as a second try.

JG

[Qlik_UserEM]

Thank you, this worked perfectly.