Previous Term Average using Set Analysis

[#1]

Hi All,

Please help me with the following requirement which is very much required to be solved.

We have to calculate the Previous Term Average and use it for calculation.

following is required

Year Term Amount Previous Term Average

2009 2009 1st Term 100 -

2009 2nd Term 200 100/6

2010 2010 1st Term 300 200/6

2010 2nd Term 400 300/6

So when 2009 2nd term is selected, the average of previous term has to be used for the calculation.

I have to acheive the above requirement without the use of Above() Function. Can this be done using SET Analysis?

Please help me in this regard.

[#2]

Hi,

you can do this with set analysis, but you may need a month-year field...I guess a term is a six-month period.

could you send an image of your model?

Regards!

[#3]

Yes,

I?have Year Month Field(YM).

Yes, Term is a Six Month Period.

For Security reasons i cannot share the Model. Please understand my situation.

Thanks you very much for responding. 🙂

Yes, using this YM field, and when the term changes, how can we get the above requirement?

[mike_garcia]

Its gonna be a bit complex if you use Set Analysis based on a Field that is part of the Chart Dimensions. You can achieve what you mention using interrecord functions like above() or before().

Mike.

[#5]

Thank you Mike for the response.

But Above() or Before() functions are always dependent on the previous value being displayed in the UI.

When Filtering is done, then no value is shown since before() or Above() value is not available.

But, still i am trying with various options like at Scripting level using the Previous() Function.

But if Previous() function is used, i have to group by Product or something. But, everytime new dimension analysis has to be done, this has to be changed. I dont want to do in that way.

Please help!!!

[#6]

HI you can use this condition::::::: also in same way you can make Half Yearly as required !term and 2 Term

YTD--------------
COUNT(DISTINCT{$<[CAL YEAR] = {$(=max([CAL YEAR]))}, Cal_Month_No
={"<=$(=max({<[CAL YEAR]={$(=max([CAL YEAR]))}>} Cal_Month_No))"}>}
[CASEID])


MTD--------------
COUNT(DISTINCT {$<GEO={'APAC'},[CAL YEAR] = {$(=max([CAL YEAR]))},
Cal_Month_No = {"<=$(=max({<[CAL YEAR]={$(=max([CAL YEAR]))}>}
Cal_Month_No))>$(=max({<[CAL YEAR]={$(=max([CAL YEAR]))}>}
Cal_Month_No)-1)"}>} CASEID)

QTD---------------
COUNT(DISTINCT {$<GEO={'APAC'},[CAL YEAR] = {$(=max([CAL YEAR]))}, Cal_Qtr =
{$(=MaxString({<[CAL YEAR]={$(=max([CAL YEAR]))}>} Cal_Qtr))}, Cal_Month_No
= {"<=$(=max({<[CAL YEAR]={$(=max([CAL YEAR]))}>} Cal_Month_No))"}>}
CASEID)

[#7]

Hi Ashwin,

Thank you very much the reply.

But, I am sorry to inform you that my requirement is not the one which is stated.

The calculations provided by you are yeilding me Amount Till selected month i.e. the total amount is till that particulr month which is selected.

But for every month, based the existence of the month, i have to fetch the average amount of the previous term.

If 200804 is selected, then Average amount of 2007 2nd Term has to be fetched and if the month is 200810 then average amount of 2008 1st Term has to be fetched.(Pervious Term Average is required and not till date)

Still trying :(...hope i will be able to do it.....

[#8]

HI,

Here is a solution (see attachment)

best regards

[#9]

Hi Gabriela Morales,

Thank you for the solution provided. The Solution provided by you is actually what i want. Amazing understandablility :)..you rock in Qlikview.

But, somehow when i am trying to use excel as my source instead of Inline Wizard, no values are being displayed.

Actually i do not have Date field, but still tried with Date(Year) which got me Date. But, Year(Date), Month(Date), Monthname(Date) does not yield any data. Somehow not able to figure it out.

Used the same formats(Date Format, MonthNames, DayNames etc) as you used in the application.

When i use the inline wizard it works amazingly. But, when excel or database of used as source, then it does not work.

However, really appreciate your appraoch.

Kindly request to guide me in this regard.

[#10]

I have YM Field which is of the Format YYYYMM.

199001, 199004 etc.................

once i get the previous term average, i have to display it with respect to Product, Customer etc.

Please help me in this regard.