Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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'}
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,
Is your current result false or null?
The result is false. The number it shows is wrong.