topic Re: 3 Month Prior Sales in set-analysis/Scripting in QlikView

3 Month Prior Sales in set-analysis/Scripting

abhaytrip — Mon, 28 May 2018 11:18:37 GMT

Hi folks,

I'm stuck in a problem where I need to present the 3 Month prior sales (including the 3rd Month) in a straight table , but im unable to do so.

here im attaching the image where you can find the sample example of the problem stated above.

any sort of help is appreciate.

Thanks,

Abhay

Re: 3 Month Prior Sales in set-analysis/Scripting

chinnuchinni — Mon, 28 May 2018 11:23:24 GMT

try this:

=Sum({<Month-Year={">=$(=AddMonths(Max(Month-Year),-3)) <$(=Max(Month-Year))"}>}Sales)

Re: 3 Month Prior Sales in set-analysis/Scripting

abhaytrip — Mon, 28 May 2018 11:48:01 GMT

tried this , but it doesn't seem to work.

I used this formula.

=num(Sum({<[Month-Year]={">=$(=AddMonths(Max([Month-Year]),-3)) <$(=Max([Month-Year]))"}>}Sales)/1000,'$###.##k')

Re: 3 Month Prior Sales in set-analysis/Scripting

sunny_talwar — Mon, 28 May 2018 13:04:31 GMT

May be try this

Above(Sum(Sales), 3)

Missing Manual - Above() and Below()

Re: 3 Month Prior Sales in set-analysis/Scripting

abhaytrip — Tue, 29 May 2018 06:11:27 GMT

hey this works if i dont make any selection from the month or year.

but as you see if i select a particular year i wont be able to see data for JAN FEB MAR as according to the formula there's nothing above it.

Any other alternative would also work .

Thanks alot.

Re: 3 Month Prior Sales in set-analysis/Scripting

devyanshu_gupta — Tue, 29 May 2018 06:52:32 GMT

Hi,

I prefer using a Month Number in the calendar for these type of queries.

MonthNum=Year*12 + Month.

Now using this you can calculate the value in Set

Try Sum({<MonthNum={Max(MonthNum)-3},Month=,Year=>}Sales)

Regards,

Devyanshu

Re: 3 Month Prior Sales in set-analysis/Scripting

abhaytrip — Tue, 29 May 2018 07:03:50 GMT

I did try using this expression, It din seem to work out.

Sum({<MonthNumber={'Max(MonthNumber)-3'} ,Month=,Year=>}Sales)

Re: 3 Month Prior Sales in set-analysis/Scripting

rodjager — Tue, 29 May 2018 07:27:07 GMT

Hi Abhay,

I have done this before where I used a resident load to bring the data back in as a comparative figure. It gives more flexibility for doing period comparisons and as far as I know it's more efficient than a complex expression with set analysis.

I have attached a simple model of how this could work.

Hope this helps.

Rod

Re: 3 Month Prior Sales in set-analysis/Scripting

abhaytrip — Tue, 29 May 2018 08:29:07 GMT

=If(Sum(Sales) > 0, Above(sum({<[Month-Year]>}Sales),3))

This seems to be working as expected.

Re: 3 Month Prior Sales in set-analysis/Scripting

abhaytrip — Tue, 29 May 2018 08:29:43 GMT

This works too Rod , Thanks alot

Re: 3 Month Prior Sales in set-analysis/Scripting

sunny_talwar — Tue, 29 May 2018 10:59:19 GMT

Or you can try this

Above(Sum({<[Month-Year]>}Sales), 3) * Avg(1)