Aggr() and multiple <set modifier>s

[Janneke]

Dear all,

I am visualizing a value for each month of current year (including a start value). Usually this works with

=vValueStart +Aggr(RangeSum(Above(total Sum({$<Year={$(=vYear)},Month_nr={'<=12'}>}Value),0,RowNo(total))),Month_nr)

What I want now is to do some calculations, like

=vValueStart

+Aggr(RangeSum(Above(total Sum({$<Type={1},Year={$(=vYear)},Month_nr={'<=12'}>

+<Type={2},Year={$(=vYear)},Month_nr={'<=12'}>

-<Type={3},Year={$(=vYear)},Month_nr={'<=12'}>

-<Type={4},Year={$(=vYear)},Month_nr={'<=12'}>}Value),0,RowNo(total))),Month_nr)

However, this calculation does not work. I think it has to do with the combination of Aggr() and multiple <set modifier> but I can't figure out the solution.

Can you please help me?

Thanks in advance,

Janneke.

[marcus_sommer]

I'm not quite sure how you want to combine the different Type values but maybe the following does what you want:

=vValueStart

+Aggr(RangeSum(Above(total Sum({$<Type={1,2}-{3,4},Year={$(=vYear)},Month_nr={'<=12'}>} Value),0,RowNo(total))),Month_nr)

- Marcus

[sunny_talwar]

In lay-man words, what are you trying to do here?

[Janneke]

Hi Sunny,

I have different types of values. Some of them need to be added to the startvalue and some need to be subtracted. It is not only the type that is different. There are multiple dimensions that are included in what I simplified as Type = {1}.

I want a qraph that shows all the months of the year, but the values can only be shown for the months that are already passed (for now up to May). That is why I have the Aggr and RangeSum around the set analysis.

Hope you understand what I would like to achieve here.

Greetings, Janneke.

[Janneke]

Hi Marcus,

Thank you for your response, but this is not working for me.

I have different types of values. Some of them need to be added to the startvalue and some need to be subtracted. It is not only the type that is different. There are multiple dimensions that are included in what I simplified as Type = {1}.

Do you have another approach?

Regards, Janneke.

[sunny_talwar]

Would you be able to provide some raw data and the output you are expecting to see?

[marcus_sommer]

Unfortunately it makes it not clearer what do you want to do. If I look on your answer to Sunny I'm not sure that you will need the aggr() and the above(). Periods in the future could be excluded by other ways - in a check that the date is smaller than today() or by using flags in tthe master-calendar or just by checking if certain data exists.

I suggest that you try to simplify your approach - beginning just with your inner sum() and splitting your Type-conditions into several expressions, like here simplified:

sum({< Type = {1}>} Value) + sum({< Type = {2}>} Value) - sum({< Type = {3}>} Value) ...

If those single-parts work like expected you could think of combining them and to shorten/simplify them into lesser expressions.

- Marcus

[Janneke]

Hi Marcus,

You are right. That makes it easier.

Only problem with leaving the aggr() and above() out, is that the future months of the year will have the data of the last month, and I want those months visible on the graph, but without data.

Thank you for your help so far.

Janneke.

[marcus_sommer]

It depends on various things if and how missing values and NULL's could be displayed. Maybe it's enough to disable the option of hiding NULL within the object-properties in tab presentation. Otherwise you could try to include your simplified and working expressions within the aggr again.

- Marcus