Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
frank1982
Contributor III
Contributor III

Function Aggr() with five dimensions

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:

Input.png

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

Gruppo.png

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.

1 Solution

Accepted Solutions
danosoft
Specialist
Specialist

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

😉

View solution in original post

8 Replies
sunny_talwar

Which two dimensions?

Capture.PNG

frank1982
Contributor III
Contributor III
Author

The bottom two, GIORNO and N_ORA

sunny_talwar

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

frank1982
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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 :

Input2.png

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

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
Contributor III
Contributor III
Author

Yes!

It works very well!

Thank you Daniele!