Date/week display in reports

[#1]

Hi,

Can someone please guide me on how to achieve the below in Qlikview:

--- I want to display a pivot table report with data against years, where in the columns for the current month + 2 months should be displayed as individual date, and from the 4th month, it should be displayed as week number.

Example:

                           Year                                                                     2015               

                           WeekNo           46                46                    46              .........     47      48     49    50  

                           Date               11/11/2015   12/11/2015    13/11/2015       .........      -        -         -       -

S.NO  description    

1          abc                                     1                0               4                                  11     15       8     20

On loading the report, it should be automatically expanded date wise for current month+ 2 months and following that data should be collapsed for week wise.

[Gysbert_Wassenaar · Accepted solution]

See this discussion: Re: Consolidating Months into Quarters. You should be able to apply the same technique to dates and weeks.

[#3]

That helps! Thank you, Gysbert Wassenaar!

[#4]

Hi, I need some help again. In the link that you provided ( month - quarter split), below expression was used to do the split.

=If(Month<Ceil(Max(TOTAL Month),3)-2,Quarter, Month)

I'm confused as to how to modify it for date-week since some months have 30 days, some 31 and feb 28/29 ,depending on leap year or not ,( as opposed to month-quarter scenario where by standard fixed 3 months can be grouped into a quarter)

Please provide some assistance.

[Gysbert_Wassenaar]

Perhaps something like this:

If(Date>=Monthstart(Today()) and Date<MonthEnd(Today(),2),Date, Week)

If not, please post a small qlikview document that illustrates the problem.

[#6]

I will try this expression and get back. Thanks.

[#7]

Hi,

I tried the expression u stated and it works. I have successfully generated a row with date and weeks based on a condition.

Now, I have another requirement where, there needs to be a row below this date/week row that will be offset by a variable.

Example:

Inputbox- takes a variable entered by user. EX: 7 (and will refer to 7 days)

Row 1- Date/week display based on current date

Row 2 - Date/week display Row 1 value offset by "7"  days

I tried with the same expression and subtracting it with the variable from inputbox. It does not affect date and week in the same manner, also some date values are missing in the generated row.

Pls advise on how to proceed with this. Thanks!