Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
moningi13579
New Contributor III

Finding Previous Month Sales

Hi Guys,

I am having a problem in calculating the previous month sales value for  the adjusted sales.

export.png

in my data model I have used left join to join orders and returns table with Order ID, the return table is having (Order ID, Status, Return Date) what is need is to find the adjusted sales which means the (Total sales- total sales of the product that have been returned), so I am able to find the adjusted sales for current month but I am not able to find the adjusted sales for previous month.

I am using these expressions:

MinDate = Min([Return Date])

MaxDate= Max([Return Date])

vPreviousYearRD= Year(AddMonths(Date(MinDate),-1)) // to find the year

vPreviousMonthRD=Month(AddMonths(Date(MinDate),-1))// to find month

vPreviousMonthMin = Min({<Month={'$(=vPreviousMonthRD'},Year={$(=vPreviousYearRD)}>}[Return Date])

vPreviousMonthMax= Max({<Month={'$(=vPreviousMonthRD'},Year={$(=vPreviousYearRD)}>}[Return Date])

Adjusted Sales= sum({<[Return Date] = {'>= $(=vPreviousMonthMin) <=$(=vPreviousMonthMax)'}>}Sales)

and I am using the filter of year and month which are generated using Order Date.

Can some one help me out in this please, it is urgent.

Thanks in advance,

Vivek

5 Replies
Not applicable

Re: Finding Previous Month Sales

Here

vPreviousYearRD= Year(AddMonths(Date(MinDate),-1)) // to find the year

vPreviousMonthRD=Month(AddMonths(Date(MinDate),-1))// to find month

Try using the MaxDate.

Hope this helps

MVP
MVP

Re: Finding Previous Month Sales

You may need to clear user selections in the calendar fields to avoid an incompatible selection state in your set expression:

sum({<[Return Date] = {'>= $(=vPreviousMonthMin) <=$(=vPreviousMonthMax)'}, [Order Date], Month, Year>}Sales)

Re: Finding Previous Month Sales

In addition you might need to add Date() function to your variables or into your set analysis directly

vPreviousMonthMin = Date(Min({<Month={'$(=vPreviousMonthRD)'},Year={$(=vPreviousYearRD)}>}[Return Date]), 'DateFieldFormat')

vPreviousMonthMax= Date(Max({<Month={'$(=vPreviousMonthRD)'},Year={$(=vPreviousYearRD)}>}[Return Date]), 'DateFieldFormat')

Dates in Set Analysis

moningi13579
New Contributor III

Re: Finding Previous Month Sales

Hi Guys,

If I am using the expression Sum({<Month={'$(=vPreviousMonthRD)'},Year_RD={$(=vPreviousYearRD)}>}Sales) it is giving me the value for the selection made on year and month but if I select year=2010, month=jan it is giving me the value =0 , it should give me a value. How can I get it any suggestions.

Thanks,

Vivek

MVP
MVP

Re: Finding Previous Month Sales

Could you post a small sample app or at least post a complete scenario (like your selections you are doing, all resulting variable values, also the sample records that should be filtered by your set expression)?

Community Browser