Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Re: YTD formula not working

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

aresb

Creator

2023-04-17
06:08 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 ?

711 Views

24 Replies

aresb

Creator

2023-04-17
08:52 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Can you show me with an example ?

281 Views

aresb

Creator

2023-04-17
09:08 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

277 Views

marcus_sommer

MVP & Luminary

2023-04-17
09:11 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

276 Views

aresb

Creator

2023-04-17
09:19 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

271 Views

marcus_sommer

MVP & Luminary

2023-04-17
09:24 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

269 Views

aresb

Creator

2023-04-17
09:41 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

260 Views

marcus_sommer

MVP & Luminary

2023-04-17
09:48 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 ... **y**ear = ... max(Year) ...

254 Views

aresb

Creator

2023-04-17
10:03 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 ?

253 Views

marcus_sommer

MVP & Luminary

2023-04-17
10:13 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Did you try my suggestion from above?

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

249 Views

aresb

Creator

2023-04-17
10:20 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

What am i missing now ? =(

246 Views