topic Re: Evaluate different dates in a expression in QlikView

Evaluate different dates in a expression

Wed, 17 Dec 2014 12:21:16 GMT

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!

Re: Evaluate different dates in a expression

Gysbert_Wassenaar — Wed, 17 Dec 2014 12:25:35 GMT

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

Re: Evaluate different dates in a expression

Wed, 17 Dec 2014 16:25:47 GMT

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!

Re: Evaluate different dates in a expression

Anonymous — Wed, 17 Dec 2014 16:45:33 GMT

Hi Bernando,

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

Best regards,

Janusz

Re: Evaluate different dates in a expression

Wed, 17 Dec 2014 17:08:26 GMT

For sure Janusz, any doubt let me know.

Thanks!

Re: Evaluate different dates in a expression

Gysbert_Wassenaar — Wed, 17 Dec 2014 18:50:16 GMT

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

Re: Evaluate different dates in a expression

Wed, 17 Dec 2014 19:09:05 GMT

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!

Re: Evaluate different dates in a expression

Gysbert_Wassenaar — Wed, 17 Dec 2014 19:22:25 GMT

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.

Re: Evaluate different dates in a expression

sebastiandperei — Wed, 17 Dec 2014 19:38:20 GMT

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.

Re: Evaluate different dates in a expression

Mon, 05 Jan 2015 18:51:27 GMT

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!

Re: Evaluate different dates in a expression

sebastiandperei — Thu, 29 Jan 2015 13:33:49 GMT

Ok, si funcionó, marcá el post como respondido, así se cierra.