YTD formula not working

[aresb]

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 ? 

[vikasmahajan]

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)

[marcus_sommer]

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.

[aresb]

hi, 

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

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

[BrunPierre]

Maybe using the date field.

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

[marcus_sommer]

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.

[aresb]

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

[marcus_sommer]

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).

[aresb]

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).

[marcus_sommer]

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.