topic Re: Function Aggr() with five dimensions in QlikView

Function Aggr() with five dimensions

frank1982 — Wed, 30 May 2018 15:21:31 GMT

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.

Re: Function Aggr() with five dimensions

sunny_talwar — Wed, 30 May 2018 15:42:04 GMT

Which two dimensions?

Re: Function Aggr() with five dimensions

frank1982 — Wed, 30 May 2018 15:44:44 GMT

The bottom two, GIORNO and N_ORA

Re: Function Aggr() with five dimensions

sunny_talwar — Wed, 30 May 2018 15:47:26 GMT

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

Re: Function Aggr() with five dimensions

frank1982 — Wed, 30 May 2018 15:56:16 GMT

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

Re: Function Aggr() with five dimensions

sunny_talwar — Wed, 30 May 2018 16:06:49 GMT

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?

Re: Function Aggr() with five dimensions

frank1982 — Fri, 01 Jun 2018 17:16:33 GMT

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.

Re: Function Aggr() with five dimensions

danosoft — Tue, 05 Jun 2018 06:35:06 GMT

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

😉

Re: Function Aggr() with five dimensions

frank1982 — Tue, 05 Jun 2018 07:36:02 GMT

Yes!

It works very well!

Thank you Daniele!