Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have the following expression
sum({<DocType={'A'}>} Value1)*-1 + sum({<DocType={'A'}>} Value2)
I have Data from Jan 2013
I have Date field in my table
I have to show the above expression in KPI
I have given the user to select Month,Year,Quarter
What i want is
for example :- I the user selects Year=2016 and Quarter =Q1 and Month =Feb
So the value in KPI should show Value for the date range form 1 Jan 2013 to 28 Feb 2016
Note:- i do not have access to the data load editor
Thanks in advance
You mean that you want want to aggregate data in a QuarterToDate timeline where ToDate is the last day of the selected month (or the most recent month if your calendar allows multiple selecitons)?
Let's assume that you have a calendar with a field CalendarDate, then you can try adding someting like this to your set expression (in both sum() calculations):
{ ... CalendarDate={'>=$(=QuarterStart(Max(CalendarDate)))<=$(=MonthEnd(Max(CalendarDate)))'}, ...}
Two additions:
1) It seems you want to only filter the date range up to the selected month end (i.e. don't define the lower limit of the range). Hence just remove the lower limit from the numeric search.
2) If the user can make active selections in calendar fields, like Month, Year, Quarter, you need to ignore selections in these fields to avoid incompatible sets (if you are using set identifier $):
{$< CalendarDate={"<=$(=MonthEnd(Max(CalendarDate)))"}, Year=, Quarter=, Month=> }
See also
The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync