Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I'm trying to create a pivot table that uses a CreatedDate field that groups the data in the last 30 days and then groups the columns by the work week. Pretty much exactly how this SFDC report is structured. Does anyone have an idea on how I can do this?
Something along the lines of: Sum({< Project Start Date = {">Today()-30"} >} PlannedHours)
I'd suggest reading up on set analysis to better understand how to do this sort of thing in a general sense.
Well done.
Did you encounter any particular issue while doing so?
Hi, sorry, I meant to form this as a question. I'm new to Qlik and wasn't sure how to go about coding something like this.
WeekName(Date) should presumably work, though the formatting will be different. If you need this specific format you can achieve it using WeekStart() and WeekStart()+6. Do note that in a 30 day period, you could have as many as six weeks present, and the first and last weeks may be partial (at least one of them is certain to be).
Thanks for the function recommendation, although I'm not sure how I should use it. I have a field [Project Start Date] which contains dates that go back a few years. If I only wanted to show those fields where the Project Start Date is within the last month how would I do that? After that, I then would need to format the columns such that they appear in the example I mentioned above.
Something along the lines of: Sum({< Project Start Date = {">Today()-30"} >} PlannedHours)
I'd suggest reading up on set analysis to better understand how to do this sort of thing in a general sense.
Yes, I was just reading about set analysis. I'm pretty new to Qlik so I'm not very familar with it yet.
I tried using your suggestion in my expression, but I get an undescriptive error. With what little I know about set analysis, I don't get what the '>' is used for just before the Today function.
This is the code I used:
=Sum({<[Project Start Date]={">Today()-30"} >} PlannedHours)
The syntax should be correct assuming the field names are correct. The inner > means you're looking for greater-than (greater than today()-30 = last 30 days), as this is a search string.