topic Re: Finding Previous Month Sales in QlikView

Finding Previous Month Sales

Anonymous — Wed, 25 May 2016 16:12:07 GMT

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

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

Re: Finding Previous Month Sales

Wed, 25 May 2016 17:14:19 GMT

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

Re: Finding Previous Month Sales

swuehl — Wed, 25 May 2016 17:50:04 GMT

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

sunny_talwar — Wed, 25 May 2016 18:27:54 GMT

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

Re: Finding Previous Month Sales

Anonymous — Wed, 25 May 2016 19:50:01 GMT

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

Re: Finding Previous Month Sales

swuehl — Wed, 25 May 2016 20:23:51 GMT

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