Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I use set analysis a lot and get (mostly) the results I expect.
I think next expression is rather simple but it dont gives any result.
In a table I have a date and an amount.
Now I want to compare total amount with the total amount of previous month.
Date is in a listbox.
I have a variable : only(addmonths(getfieldselections(date),-1))
The variable has the correct value when I show it in a textbox
To calculate total sum of previous month I use the expression
=sum({<date={$(variable)}>} amount)
But result = -
"
Must be a very simple expression I suppose, but just don't see what I am doing wrong
It's crucial to add context to any issue description.
As far as I understood, and what your original post is missing, you are trying to use your expression in a chart with dimension date and you expect the value for the previous month to appear on the line with the current month date.
Obviously, that's out of the aggregation scope.
You can circumvent this by adding the TOTAL qualifier to your sum aggregation:
Sum( TOTAL {YourSet}>} date)
Do you have a single selection made in date field? Also, do you get the same format as your date field for the variable named "variable"? If the answer to the above two question is yes, then try this
=Sum({<date={"$(=variable)"}>} amount)
or this
=Sum({<date={"$(=$(variable))"}>} amount)
try like this
sum({<date={'$(variable)'}>} amount)
or this
=Sum({<date={"$(=Date($(variable)))"}>} amount)
It still doesn't work. Perhaps it's better I first start my weekend and try after the weekend again with a clear mind.
Thank you all for your quick respons.
What does the value of the variable show? And what does your Date field look like?
As a shot in the dark you could try:
sum({<date={'(variable)'}>} amount)
The variable has the same format as the datefield itself. For instance 1-5-2017
Can you tell us, How you define the variable?
=only(addmonths(getfieldselections(date),-1))
You need to have it ignore the date selection and just use variable. Like:
=sum({<date={'$(variable)'}, date=>} amount)