topic Re: Prior Period / Last 6 Month Calculation in QlikView

Prior Period / Last 6 Month Calculation

Mon, 08 Feb 2016 14:13:37 GMT

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!!

Re: Prior Period Calculation

sunny_talwar — Mon, 08 Feb 2016 14:20:22 GMT

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)

Re: Prior Period Calculation

swuehl — Mon, 08 Feb 2016 14:20:38 GMT

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

Re: Prior Period Calculation

Mon, 08 Feb 2016 14:56:21 GMT

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

Re: Prior Period / Last 6 Month Calculation

swuehl — Mon, 08 Feb 2016 15:16:14 GMT

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)

Re: Prior Period / Last 6 Month Calculation

Mon, 08 Feb 2016 15:27:03 GMT

Can you please amend this to the QVW file.

I have attached the source file for the QVW file.

Thanks

Re: Prior Period / Last 6 Month Calculation

swuehl — Mon, 08 Feb 2016 15:37:47 GMT

Just like I've shown above.

Re: Prior Period / Last 6 Month Calculation

Mon, 08 Feb 2016 15:50:33 GMT

Many Thanks!! It's working.

Re: Prior Period / Last 6 Month Calculation

Mon, 08 Feb 2016 17:19:41 GMT

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

Please help.

Re: Prior Period / Last 6 Month Calculation

swuehl — Mon, 08 Feb 2016 17:23:04 GMT

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?

Re: Prior Period / Last 6 Month Calculation

Mon, 08 Feb 2016 17:48:18 GMT

Extremely Sorry. It's perfectly working.