topic Re: Group date by calendar week in pivot table in QlikView

Group date by calendar week in pivot table

Red_Hare — Mon, 12 Dec 2022 15:03:58 GMT

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?

Re: Group date by calendar week in pivot table

MarcoWedel — Sat, 10 Dec 2022 12:00:33 GMT

Well done.
Did you encounter any particular issue while doing so?

Re: Group date by calendar week in pivot table

Red_Hare — Mon, 12 Dec 2022 15:03:13 GMT

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.

Re: Group date by calendar week in pivot table

Or — Mon, 12 Dec 2022 15:10:37 GMT

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).

Re: Group date by calendar week in pivot table

Red_Hare — Mon, 12 Dec 2022 15:36:47 GMT

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.

Re: Group date by calendar week in pivot table

Or — Mon, 12 Dec 2022 15:42:27 GMT

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.

Re: Group date by calendar week in pivot table

Red_Hare — Mon, 12 Dec 2022 17:07:25 GMT

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)

Re: Group date by calendar week in pivot table

Or — Mon, 12 Dec 2022 17:22:52 GMT

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.