Evaluate different dates in a expression

[#1]

Hello, I wanna make a expression in a Pivot Table, where there is only one dimension, Date.

The expression is a division between the balance today and the total balance 2 months ago.

The field Date is for any date, and Date_2 is a date 2 month before Date.

This is the expresion made:

sum(Aggr(sum( {<cod_Situation = {3} >} BuvBalance), Date))/

sum(Aggr(sum( {<cod_Situation = {1, 2, 3, 4, 5, 6, 7, 8, 9} >} BuvBalance), Date_2))

The problem is that the expression can not evaluate correctly Date_2, and the dividend does not work correctly.

In fact, looks like it evaluate Date_2 as Date.

Thanks for any help!

[Gysbert_Wassenaar]

From the help file:

There is a special function for advanced aggregations:

aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})

Returns a set of values of expression calculated over dimensions. The result can be compared to the expression column of a 'local chart', evaluated in the context where the aggr function resides. Each dimension must be a single field. It cannot be an expression (calculated dimension).

[#3]

Thanks Gysbert for the answer.

I was trying to understand how to use distinct in the expresion and reach to these expressions:

I simplified the expresion but the idea is the same.

Case 1:

sum({<cod_Situation = {3} >} BuvBalance)/

sum(Aggr(distinct(sum( {<cod_Situation = {3} >} BuvBalance)), Date, Date_2))

Case 2:

sum({<cod_Situation = {3} >} BuvBalance)/

sum(Aggr(distinct(sum( {<cod_Situation = {3} >} BuvBalance)), Date_2))

In both cases the result for the expression is 1, so it means that dividend and divisor are the same.

For the dividend expresion it take the Date value defined in Dimension, and in the divisor the same, so im doing wrong

Maybe with this new try, you have any other advise : )

Thanks!

[Anonymous]

Hi Bernando,

Would you please share a simple app to visualize what you are aiming to gain.

Best regards,

Janusz

[#5]

For sure Janusz, any doubt let me know.

Thanks!

[Gysbert_Wassenaar]

Can you explain what you're trying to show in the pivot table? What should the output be?

[#7]

Yes, for sure!

The company make a balance every day, in this expression i wanna make a indicator that compare the balance of today between the balance 2 months ago.

For that I need to divide the value of the balance today, and the value of the balance 2 months ago.

Is that helping?

Thanks!

[Gysbert_Wassenaar]

Yes, that helps. You don't want to use set analysis for this. The set is calculated at the chart level, not at the row level. You can add a small helper table to link each date with the date two months earlier. See attached qvw.

[sebastiandperei]

Intuyo que hablás español. Fijate en la última pestaña del script del qvw que adjunto.

Hay que hacer una tabla auxiliar desconectada con las fechas, y luego usás esas fechas para compararlas con Fecha y Fecha_2.

[#10]

Guys, thanks for the help!

Im still working on that, probably soon i would get the right implementation.

Any news i would update the post, thanks!

PD: Correcto Sebastian, gracias por el consejo!