topic Re: Calculation in QlikView

Calculation

dseelam — Tue, 05 Aug 2014 16:38:24 GMT

Hello All,

I need a annual calculation, the following was done in excel is it possible in straight or pivot table in qlik ?

If so in the annual column below each row is achieved by using this formula FOR C5 ROW = ((B5-B4)/(A5-A4))*365

	A	B	C
1	Date	Reading	Annual
2	05/04/12	1,403,817
3	06/13/12	388,888	0
4	07/11/12	1,458,250	0
5	08/21/12	1,487,134	257,809
6	09/23/12	1,505,500	200,370
7	10/15/12	1,525,153	332,586
8	11/16/12	1,553,282	320,117
9	12/19/12	1,577,608	269,649
10	01/17/13	1,597,829	254,955

Re: Calculation

Tue, 05 Aug 2014 16:42:31 GMT

You could do something like

=((Reading - above(reading))/(Date - above(Date))*365

Re: Calculation

dseelam — Tue, 05 Aug 2014 18:24:14 GMT

Hello Robert,

Thanks for the reply but the expression you gave me is giving different annuals from excel and what if readings are '0'

Re: Calculation

Tue, 05 Aug 2014 18:27:42 GMT

Do you have a sample qvw you could share?

Re: Re: Calculation

dseelam — Tue, 05 Aug 2014 18:48:37 GMT

Hey Robert,

Hear is the sample QVW, I am not even able to populate any data in straight table

and I am sending you sample excel sheet I am looking for with calculations

Re: Calculation

Tue, 05 Aug 2014 18:51:34 GMT

Is the annual metric you're using supposed to end up being the change in readings over the change in the number of days? What are the units you're working with?

Re: Re: Calculation

dseelam — Tue, 05 Aug 2014 20:56:08 GMT

Hey Robert,

I am trying to calculate annual trends depending on the difference between readings and difference between dates formula or definition given by user

Re: Calculation

MarcoWedel — Wed, 06 Aug 2014 00:11:06 GMT

Hi,

one solution might be:

tabInput:
LOAD Date,
    Reading,
    (Reading-Previous(Reading))/(Date-Previous(Date))*365 as Annual
FROM [http://community.qlik.com/servlet/JiveServlet/download/582931-118876/ID%20277046.xlsx]
(ooxml, embedded labels, table is Sheet1);

the reason for the slightly different results between your excel calculation and previously suggested QV solutions might be that your original post just included dates while your excel source contains timestamps with different times than 00:00:00.

hope this helps

regards

Marco