topic Set Analysis -YTD Through Prior Full Month in App Development

Set Analysis -YTD Through Prior Full Month

Qlik_UserEM — Tue, 04 May 2021 15:35:54 GMT

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?

Re: Set Analysis -YTD Through Prior Full Month

JuanGerardo — Tue, 04 May 2021 18:43:04 GMT

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.

Re: Set Analysis -YTD Through Prior Full Month

Qlik_UserEM — Tue, 04 May 2021 18:48:40 GMT

Thank you, this worked perfectly.