Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Creator

## YTD formula not working

hi everyone,

i have this formula

sum({<Year={\$(=Only(Year))},Month= {"<=\$(=max({<Year={\$(=Only(Year))}>} [Month num.]))"}>}cod_connid_2_3_4_6_7_v)

which give me this result

nevertheless the formula seems working with february data not including january results.

i should have 1540 but give me only february results.

Any idea on how to fix this ?

Labels (3)

• ### Set Analysis

24 Replies
MVP

Hi ,

Create MonthYear Filed in your script & use following set analysis for ytd:

SUM({<Year=, MonthYear= {"<= \$(=max(MonthYear))"}>} cod_connid_2_3_4_6_7_v)

If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
MVP & Luminary

The used fields Month and Monthnum aren't the same and probably contained Month a string-value like 'Feb' and not 2 like Monthnum does.

Creator
Author

hi,

i have Year, Month and YearMonth, but your formula doesn't work:

i can't undestand what im doing wrong, any idea?

Master

Maybe using the date field.

Sum({\$<DateField= {'>=\$(=YearStart(Today()))'}>} cod_connid_2_3_4_6_7_v)

MVP & Luminary

If you want to calculate with fields, like min() / max() or comparing them with <= >= the fields must be numeric - it won't work with strings.

Creator
Author

Hi,

my fields are numeric, but the above formula doesn't work anyway.

Year is a numeric field and even Month[Num]

anyway i get this result:

the correct number should be the sum of 202301+202302+202303

MVP & Luminary

Your above shown screenshot displayed it very well that YearMonth isn't numeric because the max() of it must show any value and not NULL. That all chars from a field-value are numbers doesn't mean mandatory that the value itself is also a number. For example:

year(Date) & num(month(Date), '00')

returned a string and

year(Date) * 100 + month(Date)

will return a valid number - and both versions form the example will look very similar. A very simple way to check it is to look on the alignment of the values within a table. Left aligned means strings and right aligned means it are numbers (unless you touched the layout-settings to customize the view).

Creator
Author

Hi Marcus,

the column Monthly is just a concat of fields which i use into the table to show the date.

In the set expression i use Year (which is numeric) and Month[Num.] (which is numeric too).

MVP & Luminary

If you use a logic like above your object doesn't have a YearMonth column. Further no selection of a period-field must be done - unless these fields are set to be ignored within the relevant calculation-parts. This means the main set analysis as well as the calculations within the \$-sign expansion.

Tags
Community Browser