Adjust Set Expression based on Dimension value on x-axis

[alliedarmour]

Hey all,

I'm stuck at the following problem: I have date values (month and year) on the x-axis and some Sum formula with a set expression as my measure in a bar chart.

Now, this chart always shows the last 12 months, based on the current selection of year and (optionally) month.

Now the problem is this: The values on the y-axis have 3 "versions"; so let's say value X in version 1 is 2, version 2 is 4 and version 3 is 6.

Now, for a year which is already finished I want to take only the highest version of the values, for a pending year the most recent.

Example: I select Year 2024 and month January -> the chart shows the dimensions: 01-2024, 12-2023, ..., 01-2023.

Now if the year changes to 2023 (for 12-2023) I need the latest possible version (3) and for 01-2024 the most recent (let's say 1).

I tried the following, but it didn't work out as I can't seem to sum the values depending on the x-axis:

if(Year(AddMonths(Max(discharge_date),  -12)) < Max(year),
Sum({$<ischarge_date = {">$(=AddMonths(Max(ischarge_date), -12))<=$(=Date(Max(ischarge_date)))"}, Version = {3}, MonatNummer = , year= >} Value),
Sum({$<ischarge_date = {">$(=AddMonths(Max(ischarge_date), -12))<=$(=Date(Max(ischarge_date)))"}, Version = {1}, MonatNummer = , year= >} Value)

The if-condition (logically) always evaluates to true, so this doesn't work - is there any possibility to do this kind of calculation?

Thanks in advance!

[marcus_sommer]

With your described dataset it's not solvable per set analysis because it's mixing periods with different versions - and if they need a different calculation you will need appropriate if-loops to query them and then branching into the wanted calculation. That's currently not be working as expected means that the if-query isn't correct respectively suitable, for example being not granular enough and/or querying in the wrong order or ...

I think I would tend to a different approach and flagging the information if the version is the highest one in the script, maybe with something like:

m: mapping load KEY, max(Version) from X group by KEY;

t: load *, -(Version=applymap('m', KEY, 0)) as Flag from X;

and then the Flag could be queried in the set analysis and/or the field could be used as selection and/or as multiplier to the relevant calculations (instead of a simple TRUE/FALSE flag the field might be also created as offset-value to the highest version).

[alliedarmour · Accepted solution]

For anyone maybe interested, I found a (bit hacky) solution, so Marcus' idea to partly precalculate the result in the script would be the more viable option.

I created a variable with a ValueList of the user-selected month and the 12 previous month and joined them with Concat() - this is my synthetic dimension for the chart.

That dimension can now be used in a Pick/Match clause in the chart script to pick the right version - if the year is the current year, pick the actual month, else if the year is already over, pick the latest version.