Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts
I have the below formula but it is not working. This KPI should only show values if the selected YearMonth is greater equal to 01.07.2023. All filtered months like '2023-Jun', '2023-May', etc should be ignored, without any value. Any ideas how to get the formula working? Thank you.
sum(
{<[YearMonth] >= {"$(=MonthEnd(Max(YearMonth))>='01.07.2023')"}>}
Amount)
I've added a variable vCutoffYearMonth = Date(MakeDate(2023,7), 'YYYY-MMM').
Try this:
{<YearMonth = {"$(=If(Max(YearMonth) >= vCutoffYearMonth, '>=$(vCutoffYearMonth) <=$(=Max(YearMonth))'))"}>} Sum(Amount)
This requires you YearMonth field to be a date field:
{<YearMonth = {">=$(=Date(MakeDate(2023,7,1), 'YYYY-MMM'))"}>}
I used MakeDate to get a date. You can use other fields or functions.
Thanks, this gives me always zero as result...any ideas?
I need something like this:
if(max([YearMonth])>=Date(MakeDate(2023,7,1), 'YYYY-MMM'),
sum({<[YearMonth]={"$(='>=' & '01.07.2023' & '<=' & MonthEnd(Max(YearMonth)))"}>}
Amount))
But it gives me only 2023-Dec value, I have selected '2023-Dec' as YearMonth filter value. I would like to see values from '2023-Jul' onwards if the selected YearMonth filter value is >= '01.07.2023'. If not, I don't want to see any values. Any ideas?
Please carify these points:
1.YearMonth comes from autocalendar and has 'YYYY-MMM' format. It is derived from timestamp. But autocalendar format it to YYYY-MMM.
2. Rolling 12 months is based on YearMonth, coming from the autocalendar. The rolling 12 months based on this dimension YearMonth is included in the formula above.
Is this now clear?
tried this but still not working! A filtered YearMonth value of '2023-Jun' should not give any result. Only a filtered YearMonth value of '2023-Jul' and greater than '2023-Jul' should give a result:
sum(
{<[Timestamp.autoCalendar.YearMonth]={"(='>=' & '01.07.2023')"},
[Timestamp.autoCalendar.YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(Timestamp.autoCalendar.YearMonth),-11)) & '<=' & MonthEnd(Max(Timestamp.autoCalendar.YearMonth)))"}
>} Amount)
I've added a variable vCutoffYearMonth = Date(MakeDate(2023,7), 'YYYY-MMM').
Try this:
{<YearMonth = {"$(=If(Max(YearMonth) >= vCutoffYearMonth, '>=$(vCutoffYearMonth) <=$(=Max(YearMonth))'))"}>} Sum(Amount)
Thanks,
This now my formula:
I have filtered on September 2023, I expect now to see the values from Jul-2023 until Sep-2023.
I only see the filtered month Sep-2023:
got the solution: vCutoffYearMonth= '01.07.2023'.