I have some data about our employees. Every month I get a new file with the month and year of creation in the filename. Now in every record of our employees there is a field called "rfte". We want to show a table with in the first colom the name of our employees, the second colom the rfte value of this month and in the third colom the rfte value of the previous month. So in my dashboard I have a selection box where you can select the month.
Now, in the script i already created a field "date" that gets the date out of the filename. I also created a field datePreviousMonth using the ADDMONTHS function to substract one month. This all seems to be working good. When I create a textfield and let it show the value of the datePreviousMonth, it shows the previous month when i select the current month in my selection box.
In the table I i made, the dimension is name, the first expression is "=rfte" and to show the data of the previous month i added a second expression
=sum({$<date={datePreviousMonth}>} rfte)
But this doens't seem to be working... I also tried
And I also have a second question. We are only interested in people where the rfte value is different this month than the value of last month, so is there a way to add restrictions inside a table?
In essence what you seem to be doing with your formulae is compare a value 'date' with itself minus 1 month - which isn't ever going to match.
When it comes to intra-month calculations such as this I prefer to create the month via a calculation around today() as that way we don't get the structural issues. So try something along the lines of the following (start with an 'if' then work to Set Analysis):
=sum(if(date=addmonths(today(),-1))
Of course you will need to ensure the formats of both values match as well.
In essence what you seem to be doing with your formulae is compare a value 'date' with itself minus 1 month - which isn't ever going to match.
When it comes to intra-month calculations such as this I prefer to create the month via a calculation around today() as that way we don't get the structural issues. So try something along the lines of the following (start with an 'if' then work to Set Analysis):
=sum(if(date=addmonths(today(),-1),rfte))
Of course you will need to ensure the formats of both values match as well.
The problem is that it's not only for the current and previous month that I have data, but also for 2, 3, 4, ... months in the past, and I have a selections box where I can choose the month, so it has to work with the value of the selected month.
