Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Evening,
I'm, trying to do something like that:
=Sum(if(MovementType='Sales' and Year(Date)=Max(Year(Date)),Sales))
MovementType = it's to identify the type of Fact
I want to Sum the sales of the current year in the selection and the previous year.
Can you show me why this is not working but the expression says ok?
Best Regards,
Ricardo Perfeito
Thank you all for showing me the way!!!.
Yuri:
Your expression is missing one )
Sum({< MovementType= {"Sales"}, Year={'$(=Max(Year(Date)))'}>} Sales)
However i made a different approach and i want to share with you.
I have a master calendar, so i don't need to add a column in my fact table. The Year is a column that i have in my master calendar table. I link the fact table through the column Date to the master calendar.
What do you think about this solution?
Awesome forum!!!
Best Regards,
Ricardo Perfeito
Try it,
Sum({< MovementType= {"Sales"}, Year={'$(=Max(Year(Date))'}>} Sales)
Create a column with a Year of date.
The reply by Yuri as correct.
Here though is the explanation:
Your expression is correct (there are no gramatical errors in it) so it wil say that the expression is OK.
However, you are not selecting a Year you are only comparing one function's value to another function's value
Year(Date)=Max(Year(Date))
It should be Year =Max(Year(Date)), where Year is a selectable column.
your table should look something like this:
MovementType | Year | Sales |
Sales | 2012 | 100 |
Purchase | 2012 | 200 |
Purchase | 2013 | 150 |
Sales | 2013 | 250 |
Sales | 2013 | 300 |
Purchase | 2013 | 450 |
Thank you all for showing me the way!!!.
Yuri:
Your expression is missing one )
Sum({< MovementType= {"Sales"}, Year={'$(=Max(Year(Date)))'}>} Sales)
However i made a different approach and i want to share with you.
I have a master calendar, so i don't need to add a column in my fact table. The Year is a column that i have in my master calendar table. I link the fact table through the column Date to the master calendar.
What do you think about this solution?
Awesome forum!!!
Best Regards,
Ricardo Perfeito