Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

3 Month Prior Sales in set-analysis/Scripting

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.

3MonthProblem.PNG

any sort of help is appreciate.

Thanks,

Abhay

1 Solution

Accepted Solutions
Highlighted
Creator
Creator

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

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



This seems to be working as expected.

View solution in original post

10 Replies
Highlighted
Creator III
Creator III

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

try this:


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

Highlighted
Creator
Creator

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

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')

3MonthProblem1.0.PNG

Highlighted

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

May be try this

Above(Sum(Sales), 3)

Missing Manual - Above() and Below()

Highlighted
Creator
Creator

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

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.

3MonthProblem1.1.PNG

Any other alternative would also work .

Thanks alot.

Highlighted
Partner
Partner

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

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

Highlighted
Creator
Creator

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

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

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


3MonthProblem1.2.PNG

Highlighted
Partner
Partner

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

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

Highlighted
Creator
Creator

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

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



This seems to be working as expected.

View solution in original post

Highlighted
Creator
Creator

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

This works too Rod , Thanks alot