topic Re: Amount to Date in QlikView

Amount to Date

Thu, 07 Feb 2013 14:07:51 GMT

Hello.

What I do is an expression that calculates for each value of temporal dimension of the chart all movements from the beginning of time to date just before the period shown.

I have this formula, but it works:

sum({<Estado={"Tesoreria"},TipoImporte={"Banco"},Fecha={"<=$(vFechaHastaPeriodo)"},[Operating Class]={8,3}, Mes=, Año= >} Importe) - Sum({<[Operating Class]={'3'}, BancoBloqueado={0}, Fecha=, Mes=, Año=>} Limite)

vFechaHastaPeriodo would be just the day before the first value dimension.

Suppose that we are seeing the year 2013 and the month of January, but broken down by weeks because the first week would be until 31/12/12, the second week would be to date 06/01/13, the third week would be to date 13/01/13, ....

I will upload an example document (with the simplest formula) and excel that loads information.

For this example, if I'm not mistaken, would have to go to day 1 January 2013 the figure of € 623,824 (corresponding to all movements prior to that date, € 373,824, less than the limit of banks € 250,000). For the 2nd of January, it would be the same data but adding the movements of the day January 1st (€ 2,502).

NO I better have two rows, one with the accumulated balance and another line with the movements of the period for calculating the next column summing these values, because that is the solution I had before, but it turns out that the limits will also vary in date . The formula will be more complex, but I wanted to know how to fix this part to apply a similar logic to the limits.

Thank you very much, not only those who respond, but also to read the post.

A greeting.

Re: Amount to Date

pover — Thu, 07 Feb 2013 14:52:30 GMT

Daniel,

Set Analysis doesn't respect dimension values. Have you looked into a solution called Asof tables? Search the topic in community to see if that will help you.

Karl

Re: Amount to Date

Thu, 07 Feb 2013 15:38:54 GMT

Una pregunta, las cantidades acumuladas tienen que ser dadas para cada día o pueden ser para cada semana? Otra pregunta, no te vale si se cargan en el script los acumulados?

Re: Amount to Date

Fri, 08 Feb 2013 09:22:16 GMT

Mira a ver si te vale esto basado en la ASOF TABLES

Re: Amount to Date

Fri, 08 Feb 2013 09:48:02 GMT

Muchas gracias Pedro.

De momento te la marco como solución útil, pero seguramente te la marque en un futuro de correcta.

Para el tema de los movimientos hasta una fecha, será una solución válida, tengo revisarlo, pero tiene muy buena pinta. Tendré que darle una vuelta a los límites por banco, porque son válidos por periodo, pero eso ya intentaré calcularlo.

Muchísimas gracias de nuevo.

Un saludo.

Re: Amount to Date

Fri, 08 Feb 2013 09:57:00 GMT

A mandar.