Function Aggr() with five dimensions

[frank1982]

Hi,

I have to create a what-if simulation.

So, I insert a value into a variable (Qta_Sim_Base) thanks to the following InputBox:

Now. I have to visualize, into a Linear Table, this value, Aggregate for multiple Time Dimension (called Temporale2😞

For each hour (Field N_ORA) I have to add the value of the variable Qta_Sim_Base.

For example, if I insert the value 1 into the input Box, each hour take the value 1, each day (Field GIORNO) have to take the value 24 etc. etc.

I tried to develope two different formulas, but don't work :

1. Sum(Aggr(Qta_Sim_Base,ANNO,TRIMESTRE,MESE,GIORNO,N_ORA)) --> it's ok for only two dimensions

2. Sum(Aggr(Qta_Sim_Base,$(=GetCurrentField(Temporale2)))) --> the value is always the same for each dimension.

Could someone help me?

Thanks in advance.

[sunny_talwar]

Which two dimensions?

[frank1982]

The bottom two, GIORNO and N_ORA

[sunny_talwar]

What do you get for the other dimensions? 1 or something else?

[frank1982]

I have to get the sum, for example:

INPUT --> Qta_Sim_Base=1

Qta_Sim_Base with Dimension N_ORA --> 1 for each hour

Qta_Sim_Base with Dimension GIORNO --> 24 for each day

Qta_Sim_Base with Dimension MESE --> (24 * 'day of the month') for each month

Qta_Sim_Base with Dimension TRIMESTRE --> (3 * 24 * 'day of the month') for each quarter

Qta_Sim_Base with Dimension ANNO --> (4 * 3 * 24 * 'day of the month') for each year

Thanks

[sunny_talwar]

I understand that, but I am not sure why you are not able to get this... is there a sample you can share for us to check out?

[frank1982]

I'm sorry,

You're right, I don't explain you all the project, but I thought that it fault because the aggregation doesn't work.

Instead now I have seen that the problem caused by a filter.

In particular, I've to get the sum of Qta_Sim_Base ONLY for an interval of time that I input through other two variables, DataDa and DataA :

Here, the value of Qta_Sim_Base has to apply for each hour of the upper period (Gen-19), and aggregated for each over-period.

I tried two different ways:

1. If statement --> =if(Year(DataDa)<=ANNO and Year(DataA)>=ANNO and Ceil(Num(Month(DataDa))/3)<=Num(Right(TRIMESTRE,1)) and Ceil(Num(Month(DataA))/3)>=Num(Right(TRIMESTRE,1)) and Num(Month(DataDa))<=MESE and Num(Month(DataA))>=MESE and Day(DataDa)<=GIORNO and Day(DataA)>=GIORNO, Aggr(Qta_Sim_Base,ANNO,TRIMESTRE,MESE,GIORNO,N_ORA),0)

2. Set Analysis --> Sum({<ANNO={">=$(Year(=$(DataDa)))"},ANNO={"<=$(Year(=$(DataA)))"},MESE={">=$(Num(Month(=$(DataDa))))"},MESE={"<=$(Num(Month(=$(DataA))))"},GIORNO={">=$(Day(=$(DataDa)))"},GIORNO={<=$(Day(=$(DataA)))"}>}Aggr(Qta_Sim_Base,ANNO,MESE,GIORNO,N_ORA))

but I can't find the right way.

Could you help me?

Thank you and I'm sorry for the inaccuracy of previous description.

[danosoft · Accepted solution]

Hey i think you can try with that:

Sum({<ANNO={'>=$(=Year(DataDa))<=$(=Year(DataA))'},MESE={'>=$(=Num(Month(DataDa)))<=$(=Num(Month(DataA)))'},GIORNO={'>=$(=Day(DataDa))<=$(=Day(DataA))'}>}Aggr(Qta_Sim_Base,ANNO,MESE,GIORNO,N_ORA))

😉

[frank1982]

Yes!

It works very well!

Thank you Daniele!