Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a filter drop down (YTD, QTD, MTD, WTD) and Year, Month, Day, Week Filter.
When the user selects either YTD or QTD, or MTD or WTD ( no Year, month, day, week filter) then show the sum of sales.
But when they select YTD and Year filter = 2023, then show sum of sales for Jan 1, 2023 - Dec 31, 2023.
Likewise it can be YTD and Year= 2023 and Month = 3 then show sum of sales for Jan 1, 2023 - march 31, 2023.
you have to use variable
for example:
vYTD = Year(Max(your_dataField))
vMonth = Month(Max(your_dataField))
vDtIni = Makedate($(vYTD),11);
vDtEnd = MonthEnd(Makedate($(vYTD),$(vMonth) , 1) );
your expression : Sum({< Date_Field = {">=$(vDtIni)<=$(vDtEnd )"} >} Your_measureField)
Thanks for the solution.My fiscal year is not from Jan - Dec. It is from April - march.
I do have Fiscal Year. Fiscal Month, Fiscal Quarter, Fiscal Date fields.
Question -
if I select only YTD, then
vYTD = Year(Max(your_dataField)) . This will be 2024
Month = Month(Max(your_dataField)). This will be 07 (July) .
vDtIni = Makedate($(vYTD),11); , This will be 11/01/2024 . ( Nov -1 , 2024 ) .
Why 11 ?
vDtEnd = MonthEnd(Makedate($(vYTD),$(vMonth) , 1) );
I want end date as of yesterday , not till end of month.
vDtIni = Makedate($(vYTD),1,1); ( forgot one coma, to be 1/1/Your_year)
if your fiscal year start on march you can force to be March not January
i force to be end of month they selected. if want to force to be YTD restrict to yesterday date
vDtEnd = Date(Today()-1);
if your fiscal year start on march you can force to be March not January
Yes I can do that in my case : vDtIni = Makedate($(vYTD),1,3);
But when i select now Year filter = 2023 ( with YTD selection intact) , Month = May , then I need YTD dates of course to be May 1 - may 31 to be evaluated in set analysis.
In that case this variable vDtIni = Makedate($(vYTD),1,3); will always default to mar 1, 2023. How to solve this ?