topic How to Calculate SMA using QlikView in QlikView

How to Calculate SMA using QlikView

Wed, 03 Jun 2009 19:15:21 GMT

Hi All,

I want to calculate Simple Moving Average for Stock for last 90 Days. I cannout use the Average function bcz for Weeends I dont have data and also on Bank Holidays I dont hvae data. For Ex. On EveryMonday I will have previous value as of Friday rather than Saturaday/Sunday, or you can compare it with the Stock Exchange.

Using Average I am getting Incorrect Values bcz as on Monday this week I want to pick data for all working 90 days, and if I change my date selections from Monday to Last week Friday, so Moving Average should be calculated from Friday -90 Working Days from there.

I have tried using the Running Totals, but its not working, Below is what I am doing:

Load DISTINCT Dates RESIDENT A; // To get the Distince dates bcz I might have multiple transaction on a given dates.

Load RowNo(), Dates RESIDENT A; ORDER BY Dates DESC;// This will give me the no's on dates in Descending order.

Now while displaiyng data how I want to calculate Moving Average. Let me know how to write it in a Script or in a CHART Expression so that I can have correct out put.

Regards,

Amit

How to Calculate SMA using QlikView

amars — Wed, 03 Jun 2009 20:45:45 GMT

In chart you can try using Full Accumulation.

Regards

Amar

How to Calculate SMA using QlikView

Wed, 03 Jun 2009 22:12:09 GMT

I already checked this but its not working... Any other help?

How to Calculate SMA using QlikView

Fri, 05 Jun 2009 19:29:00 GMT

Do you have some data ?That could help us to help you.

Thanks

Philippe

How to Calculate SMA using QlikView

johnw — Sat, 06 Jun 2009 03:02:46 GMT

Assuming your Date field only has values for when the stock exchange is open, and your X-axis is the Date, I would assume you could Accumulate 90 Steps Back and use avg(Price)/90.