I have a sheet in which data is updated as entered by various users. The table contains values associated with dates. I want to generate a report that will contain all the values and data for all dates from the previous month. Let’s say if I want the report for February 2020, I create a virtual column with format month([Date])&year([Date]) that returns 22020, and use that as a condition for my report ( [virtual column]=(month(today())-1)&year(today()). But the report manual says that conditions determine whether or not the report would be generated at all.
How do I achieve this?
Hi @Pratyay_Rakshit! I haven’t worked a lot with reports but I thought I’d reply because I see that five days have passed and you still haven’t gotten a response.
I think there may be a problem with your expression.
month([Date])&year([Date])
would yield a text expression. If all of the records you want from February have the same text " 22020" in a column, you could pull them out but otherwise not.
Or, perhaps you’re having a problem with where you have place the condition. As I say, I’m not very familiar with reports so I hope someone else can help. If you don’t get a response, I suggest that you rephrase your question with some screenshots.
Good luck!
That is correct.
You’ll need to use the report’s start expression to choose the rows you want included.
Actually I have been able to partially achieve what I want. I’m interested with previous month’s data, not the current month actually, since entry can often be late. The month([Date])&year([Date]) expression leaves values like 32020, 22020 etc. Now I made a slice with condition that (month(today())-1)&year(today()) equals the value of the preceding expression. That actually shows the values for the previous month.
Now what I have to do is devise a mechanism for when the time transitions to a new year. The expression for previous month of January wouldn’t work in this case. Maybe a couple of if statements will do the trick.
What I am actually struggling with now is generating the reports in the way I want. I do have been able to generate one but the formatting was screwed up. A person has been particularly helpful in directing me to generate those reports, but still it needs more work from my part.
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |