I'm trying to create a table where the second column sums all the Sales across the month shown in the first column:
Sum(
{$<[Month_]={ $( Column(1) ) }>}
[Sales])
So it should look like:
Month Sales
2015M01 £150
2015M02 £175
2015M03 £160
But this isn't working - the formula above just returns zero. For the last row the Month_ entry is 2015M03, and if I enter the formula as follows it works fine:
Sum(
{$<[Month_]={'2015M03'}>}
[Sales])
I'm guessing the discrepancy is because in the second formula it recognises 2015M03 as a text string, but in the first formula it doesn't? Any ideas?
From your description above, I think your set expression is invalid when the calculation fails. There are three things that I would like to highlight.
Dollar expansion of a function must contain a equal sign in order for the function to be executed. Sum( {$<[Month_]={ $(=Column(1) ) }>} [Total Clients])
The set expression search string must quoted, even if the function would return a string. NOTE: Double quote should always be used in set expressions. Sum( {$<[Month_]={"$(=Column(1) )" }>} [Total Clients])
The function you call in the dollar expansion will be calculated as an expression total. In this case I expect that to be equal to NULL, meaning the function returns a undefined value. Try by adding the expression =Column(1) in a straight table and set Total Mode of the expression to Expression Total, the result of the total will be a dash meaning NULL.
As a summary I do not think that you pull of the intended calculation that easily. A good way to accomplish your calculation can be to add a flag in the data model to tell you which rows belong to the latest month, this way your set expression would be easier. For example if your flag is 1 for latest month per year and 0 zero for all other months the expression could look like.
Sum( {$<IsLastestMonth={"1"}>} [Total Clients])
If this does not help you resolve the issue, a sample QVW would make the it easier to give more guidance.
Dollar sign expansion is evaluated at the chart level, so not row by row.
Normally Month is a dimension and the expression is just Sum(Sales), the Sum(Sales) will be calculated for each value in the dimension. So what is Month and what are you trying to achieve?
So this was a simplified example. What I actually have is a table where I want to display year-to-date figures, where some data needs to be summed across the whole year (e.g. New Clients) and some data needs to just be taken at the end of the month (e.g. Total Clients).
I should point out that all my data is held monthly, so summing across the month doesn't really add any values together but just takes the single value for that month.
I've calculated a "Last month" field, which is the last month in each year which I have data for. I then sum New Clients across the whole year (because it is all the new clients we've had in that year), but just sum Total Clients across "Last Month" - so it should just take the most up to date value of Total Clients for that year.
This is the table I want, based on the fact I have data up until March this year:
Year_ Last month New Clients Total Clients
2013 2013M12 120 120
2014 2014M12 180 300
2015 2015M03 50 350
This is what I'm actually getting:
Year_ Last month New Clients Total Clients
2013 2013M12 120 0
2014 2014M12 180 0
2015 2015M03 50 0
Last month formula
=[Year_]&'M'&
date(
max({$<[Total Clients]={'>0'}>} [Month Start])
,'MM')
New Clients formula
=Sum([New Clients])
Total Client formula (which doesn't work!!!)
Sum(
{$<[Month_]={ $( Column(1) ) }>}
[Total Clients])
Alternative Total Client formula (which also doesn't work!!!)
Sum(
{$<[Month_]={ [Last month] }>}
[Total Clients])
However if I change the formula as follows, it kinda works:
From your description above, I think your set expression is invalid when the calculation fails. There are three things that I would like to highlight.
Dollar expansion of a function must contain a equal sign in order for the function to be executed. Sum( {$<[Month_]={ $(=Column(1) ) }>} [Total Clients])
The set expression search string must quoted, even if the function would return a string. NOTE: Double quote should always be used in set expressions. Sum( {$<[Month_]={"$(=Column(1) )" }>} [Total Clients])
The function you call in the dollar expansion will be calculated as an expression total. In this case I expect that to be equal to NULL, meaning the function returns a undefined value. Try by adding the expression =Column(1) in a straight table and set Total Mode of the expression to Expression Total, the result of the total will be a dash meaning NULL.
As a summary I do not think that you pull of the intended calculation that easily. A good way to accomplish your calculation can be to add a flag in the data model to tell you which rows belong to the latest month, this way your set expression would be easier. For example if your flag is 1 for latest month per year and 0 zero for all other months the expression could look like.
Sum( {$<IsLastestMonth={"1"}>} [Total Clients])
If this does not help you resolve the issue, a sample QVW would make the it easier to give more guidance.
I agree with Toni, you're better off flagging this in the data. Like said the $ sign expansion (and the set analysis) are evaluated at the chart level, not row by row. Otherwise you would have to use a not so nice
I think what you are looking for is a simple accumulation. Try using the accumulation option under the Expressions tab.
The New Clients formula (=Sum([New Clients])) Total Clients formula (=Sum([New Clients])) are the same, but for the total Clients you use the accumulation.
