Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Noor51
Creator
Creator

Sum of Sale 6 month after a start date

I trying to get the sum of sales 6 month of the date from this field "Opportunity_Close_Date" in a KPI chart. So when the user select a customer then is show 6 month of sales after.

Here is the set expression for the KPI. 

Sum({$<Saledate = {">=$(=addmonths(Monthstart(vSaleMaxDate), -6))<$(=Monthstart(vSaleMaxDate, 0))"},
[Opportunity Stage]={'Closed-Lost','Closed-Won','Sales Handoff'},
[Opportunity Category]={' Waste & Storage Analysis'},
Opportunity_Close_Date = {"*"}>} [Contract Sales])

Any help would be appreciated. 

 

Labels (4)
4 Replies
Clement15
Partner - Specialist
Partner - Specialist

Hello,

Is your vSaleMaxDate variable dynamic according to the selected customer?
Also be careful, there is certainly an extra space before the Waste. This can distort your data if it is not wanted
{'Waste & Storage Analysis'}

Noor51
Creator
Creator
Author

Hi, 

There should be a extra space since that the way it comes into Qlik.  

The vSaleMaxDate is dynamic. We have a calendar mapping in our load script for this variable, see below for part of the code. 

Temp:
Load
min (Saledate) AS minDate,
max (Saledate) AS maxDate
Resident Salesdata;

Let vSaleMinDate = Num(Peek('minDate', 0, 'Temp'));
Let vSaleMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

 

Here is the load script for salesdate and Opportunity_Close_Date field. 

date((right([CALENDAR_YEAR_MONTH],2)&'/'&'1'&'/'& left([CALENDAR_YEAR_MONTH],4)),'M/D/YYYY') as Saledate,

date(OPP_CLOSEDATE,'MMM-YYYY') as Opportunity_Close_Date,

Clement15
Partner - Specialist
Partner - Specialist

Is your current result false or null?

Noor51
Creator
Creator
Author

The result is false. The number it shows is wrong.