Calculation

[dseelam]

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

[#2]

You could do something like

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

[dseelam]

Hello Robert,

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

[#4]

Do you have a sample qvw you could share?

[dseelam]

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

[#6]

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?

[dseelam]

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

[MarcoWedel · Accepted solution]

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