Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the below set analysis expression which calculates the Sum of Sales for the Reporting Date 31-Dec-2013.
sum({$<[Reporting Date] = {'31/12/2013'}>} [Sales])
In this I have hardcoded the value 31-Dec-2013. Instead, I need to define this expression, where the Date comes as input from a Filter selection that the user makes.
If user selects 31-Jan-2014, it should be computed for this date. Is there a way to make the expressions generic and receive inputs based on user selections?
Correct me if I'm wrong...
If a user selects 2014, he should view 2013?
You want previous year as a filter?
If your user selects a single date specifically then this basic Sum is all that is needed:
Sum( [Sales] )
May be this:
Sum({$<[Reporting Date] = {"$(=Date(AddMonths(Max([Reporting Date]), -1), 'DD/MM/YYYY'))"}>} [Sales])
This will show you 1 month prior sales based on your selection.
Also, see here:
If your scenario is more complicated than what Petter described, you can use dollar sign expansions to create dynamic values for the set modifier:
The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync
All,
The report will look some thing like this.
Sales | Sum Sales - Reporting Date 1 | Sum Sales - Reporting Date 2 |
There will be two filters.
Filter 1 : Selection for Reporting Date 1
Filter 2: Selection for Reporting Date 2
If user selects Reporting Date 1 as 31-Dec, column two should display Sum of sales for 31-Dec.
If user selects Reporting Date 2 as 31-Jan, column three should display Sum of sales for 31-Jan.
That is correct. I gave a single example, for ease of understanding. I have explained in detail in one of the posts below. I am doing trend comparisons between two dates for Sales and both dates have to come from a filter selection.
Reporting Date 1 and Reporting Date 2 are variables or do you have fields? If they are fields, are they Island Table fields?
They are fields.
Are they fields from Island table? Can you share how your data model looks?