topic Re: Preceding months qty's in QlikView

Preceding months qty's

Mon, 16 Jul 2012 11:26:16 GMT

I have a single table with the load script-

CardCode

ItemCode

DocNum

DocDate

Month(DocDate) as Month

Year(DocDate) as Year

FinncPriod

Qty

SalesAmt

...

I have a straight table where I need to show Sales Qty for the current month and also for the preceding 11 months. FinncPriod is a number that runs sequentially from the 1st posting period to last posting period of the current financial year. So transactions for May 2012 all have '110' as posting period in the table.

My formula for the Qty's for current month is -

=sum({<FinncPriod={"=Max(FinncPriod)"}>}Quantity) and it gives the correct Qty's

My problem is the formula for the preceding months. I have tried:

=sum({<FinncPriod={"=FinncPriod-1"},Year=,Month=,Date=>}Quantity) gives the same answer as current

=sum({<FinncPriod={"=FinncPriod-1"},Year=,Month=>}Quantity) gives the same answer as current

=sum({<FinncPriod={"=Max(FinncPriod)-1"},Year=,Month=,Date=>}Quantity) gives the same answer as current

I'm attaching a copy of the model herewith.

Any ideas how to solve this, please?

Regards

Danie

Re: Preceding months qty's

nstefaniuk — Mon, 16 Jul 2012 11:46:14 GMT

Hi.

In fact you need to display for each month, the value for the month and the value for the 11 months before this month.

You can use the chart function before() or above() if you want to calculate the sum of last 11 months only on values in graph.

Or if you need a real moving sum, you can follow this thread http://community.qlik.com/message/229267#229267

Re: Preceding months qty's

Mon, 16 Jul 2012 12:20:41 GMT

Thank you for the reply. Unfortunately it's not exactly what I'm looking for. If you look at the attachment with selections Year = 2012, Month = May and FinncPriod = 111, you will see that the column with the monthname 'May' has the qty's for May 2012 underneath. These qty's are correct. In the column to the left of it (with the light red shading) I want the qty's for Apr 2012 and in the column to the left of that one, Qty's for Mar 2012 and so on.

So I'm hoping to achieve this with set analysis without going through building a calender. One would think a set analysis

=sum({<FinncPriod={"=$(=Max(FinncPriod)-1)"},Year=,Month=,Date=>}Quantity)

or something similar should give me the Qty's for Apr 2012? FinncPriod after all has a unique no for all transactions in a particular month.

Regards

Danie

Re: Preceding months qty's

nstefaniuk — Mon, 16 Jul 2012 12:42:15 GMT

Try

=sum({<FinncPriod={"$(=Max(FinncPriod)-1)"}>}Quantity) and so on. And remove filters Year, Month, Day you want.

I have tried on your application and I get 105 for April, 143 for March, ...

And please, next time, don't post a 26 MB file...