# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
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.

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

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.

Vivek

5 Replies
Not applicable

## Re: Finding Previous Month Sales

Here

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

Try using the MaxDate.

Hope this helps

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)

MVP

## Re: Finding Previous Month Sales

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

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

Dates in Set Analysis

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

## 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)?