Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have current fiscal year data upto December 2019, i have to show comparison of current and previous year YTD values in a line chart. I am able to show current year values but when i write set analysis for previous year,it shows me data only till December whereas I have all months data present in previous year 2018.
current year YTD Expression:
Sum({<Date={">=$(=Date(Yearstart(max(Date),0,1),'DD-MM-YYYY'))<=$(=Date(max(Date),'DD-MM-YYYY'))"},FiscalMonth=>}[Receipt Value])
previous year YTD Expression:
Sum({<Date={">=$(=Date(Addyears(Yearstart(max(Date),0,1),-1),'DD-MM-YYYY'))<=$(=Date(Addyears(max(Date),-1),'DD-MM-YYYY'))"},FiscalMonth=,FiscalYear=>}[Receipt Value])
maybe in previous year expression, it is evaluation max date from current year and calculation its previous year hence it is showing data for previous year but only till december which is max date of current year.
what might be the possible solution to overcome this problem ?
Regards,
Aditya
May be like:
Sum({<Date={">=$(=Date(Yearstart(max(Date),-1),'DD-MM-YYYY'))<=$(=Date(Addyears(max(Date),-1),'DD-MM-YYYY'))"},FiscalMonth=,FiscalYear=>}[Receipt Value])
Hi
Try this
Previous
Sum({<Date={">=$(=Date(Yearstart(max(Date),-1),'DD-MM-YYYY'))<=$(=Date(Yearend(max(Date),-1),'DD-MM-YYYY'))"},FiscalMonth=,FiscalYear=>}[Receipt Value])
Hope this helps
Thanks
Hi,
Thanks for your reply but my starting date is getting evaluated correctly. The issue is with the ending date (the second part of set analysis) where max date of previous year could not be evaluated as it takes max date of current year and subtracts 1 year from it whereas I want the last date to be max date of previous year.
this part:
Sum({<Date={">=$(=Date(Yearstart(max(Date),-1),'DD-MM-YYYY'))<=$(=Date(Addyears(max(Date),-1),'DD-MM-YYYY'))"},FiscalMonth=,FiscalYear=>}[Receipt Value])
Regards,
Aditya
Hi
Try using Yearend as i mentioned in the above comment
Thanks
Hi Kashyap,
I used you expression but when i make a selection on a month, it is showing whole year YTD Receipt value ir from Apr to Dec
I want to show YTD based on selection
Regards,
Aditya
got the solution just used the GetSelectedCount function inside if
if(GetSelectedCount(FiscalMonth),
Sum({<Date={">=$(=Date(Yearstart(max(Date),-1),'DD-MM-YYYY'))<=$(=Date(Addyears(max(Date),-1),'DD-MM-YYYY'))"},FiscalMonth=,FiscalYear=>}[Receipt Value])
,
Sum({<Date={">=$(=Date(Yearstart(max(Date),-1),'DD-MM-YYYY'))<=$(=Date(yearend(max(Date),-1),'DD-MM-YYYY'))"},FiscalMonth=,FiscalYear=>}[Receipt Value])
)