Prior Period / Last 6 Month Calculation

[#1]

Greetings

Please find attach the QVW file.

When I am selecting the period the output is showing for current month and prior month. But if I am selecting month it's not showing prior period value.

Please advise.

Thanks!!

[sunny_talwar]

There is no data available for Year 2016 and Period 11 and 12 and that is why you are seeing 0's (or nulls in your case because Suppress When Null is checked on Presentation tab)

[swuehl · Accepted solution]

That's because your selection in months in combination with the set expressions that sets the period to prior period results in an incompatible set. You can clear selections in month:

sum({< Year = {$(vMaxYr)}  , Period = {$(vMaxPeriodH)},Month= >} qty)

Or look into a different concept:

The As-Of Table

[#4]

Thanks !! It is working.

Can you please help me to calculate the last 6 months average.

For an example when you select Dec it must give avg from July - Dec , for Nov - Jun- Nov.

Many Thanks

[swuehl]

You can either work with the AsOf table approach or using set analysis.

Since last 6 months can spread across multiple years, you should create a sequential counter in your script, something like

LOAD

     Year,

     Period,

     Month,

     Year*12+Period as PeriodNum,

     ...

FROM ...;

Then you can easily use something like

Avg({< Year = , Period =, Month=, PeriodNum = {">=$(=Max(PeriodNum)-5)<=$(=Max(PeriodNum))"} >} qty)

[#6]

Can you please amend this to the QVW file.

I have attached the source file for the QVW file.

Thanks

[swuehl]

Just like I've shown above.

[#8]

Many Thanks!! It's working.

[#9]

The Only prob is when selecting any month it's only taking that month value. 

Please help.

[swuehl]

No, it won't take only this month's value, since I cleared the selection in month field:

Avg(TOTAL  {< Year = , Period =, Month=, PeriodNum = {">=$(=Max(PeriodNum)-5)<=$(=Max(PeriodNum))"} >} qty)

Can you give an example that shows what you are telling using the sample QVW I've attached?