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
Creator
Author

Can you show me with an example ?

Creator
Author

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?

MVP & Luminary

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.

Creator
Author

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

MVP & Luminary

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.

Creator
Author

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

MVP & Luminary

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

Creator
Author

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 ?

MVP & Luminary

Did you try my suggestion from above?

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

Creator
Author

What am i missing now ? =(

Tags
Community Browser