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 ? 

[aresb]

Can you show me with an example ? 

[aresb]

Ok maybe im closer to the solution

i have this filter YearMonth which is a text field like 2023/02 etc.

for my YTD calc im using this now

RangeSum(Above(TOTAL Sum(cod_connid_2_3_4_6_7_v), 0, RowNo()))

the formula is working only if i select more than one YearMonth in my filter, as it shows in the image

but the problem remain if i select only 2023/03 in my filter.

i need to show the cumulative sum from the start of year

any idea?

[marcus_sommer]

You may have dimensions like country or product within the chart but not a period-field and then you will need to ignore any further period-selections, maybe like:

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

Beside of this I suggest to simplify the approach by transferring all possible logic into  the data-model and using flags for it. Here a discussion with a similar topic: Last Year Sales Comparison Based On The Given Date - Qlik Community - 2056536 and how to apply it within the master-calendar.

[aresb]

the year is showed correctly, but for the months doesnt work

[marcus_sommer]

An acumulation is quite different matter but your rangesum(above()) is the right way. But you need to ignore the relevant period-selections - maybe with something like (simplified):

sum({< YearMonth = , Year = p(Year)>} Value)

Means ignoring of all YearMonth selections whereby the Year takes the possible year-value behind the YearMonth selection.

Such relating to selections by simultaneously ignoring them could become quite tricky. Therefore you may to consider to avoid all such approaches and selecting always all values which should be considered or you may use an independent period-field loaded within an island-table and/or using variables to choose the wanted periods. It depends on the entirety of your data and view-requirements which approach would be the most suitable one.

[aresb]

marcus, thx for you explanations, but im really struggling and i cant undestand what im doing wrong.

i can't even understand anymore why this simple formula is not working:

Sum({<Year={$(=Max(Year))},[Month num.]={"<=$(=max([Month num.]))"}>}cod_connid_2_3_4_6_7_v)

[marcus_sommer]

It's not quite clear. By accumulating against the months you mustn't be a month-condition within the expression. Beside this you have a typo in the expression by querying ... year = ... max(Year) ...

[aresb]

ok, so then i come back to the previous approach which seems more clear to me

RangeSum(Above(TOTAL Sum(cod_connid_2_3_4_6_7_v), 0, RowNo()))

i need that 2169 only by selecting year 2023 and monthnum = 3 in my filter, and not selecting 202301 ,202302 and 202303

How can i do it ? 

[marcus_sommer]

Did you try my suggestion from above?

rangesum(above(total sum({< YearMonth = , Year = p(Year)>} YourField), 0 rowno()))

[aresb]

What am i missing now ? =(