topic Re: Calculated Week Dimension. in QlikView

Calculated Week Dimension.

renuka_sasikumar — Wed, 10 Feb 2016 06:15:34 GMT

Hi,

I have Week & Month as a field. In which I have to show the below chart.

I am confused how to describe Week.

I have one variable of vMaxMonth which will give me Max month Week.

Here For Feb Month I have Week as 5 & 6 Now I want Week 5 to be renamed as Week-1st & then goes on. its should also change according to months get changed.

Thanks in advance.

Regards,

Renuka S

Re: Calculated Week Dimension.

MK_QSL — Wed, 10 Feb 2016 06:42:10 GMT

Provide some more data.... may be sample data in excel along with the required column you need.

Re: Calculated Week Dimension.

avinashelite — Wed, 10 Feb 2016 06:54:42 GMT

if you have date field then try like this :

use week() function to generate the week number and create table with the group by over the month and have a autonumber over the same...this will create a table data according to your requirement

Re: Calculated Week Dimension.

asgardd2 — Wed, 10 Feb 2016 07:52:21 GMT

I think, you can add a new field in your data model, there calculate num of week for datefield in each month:

Week(today()) - Week(MonthEnd(AddMonths(today(),-1))) as NumOfWeekInMonth

And then use this field in dimension.

Re: Calculated Week Dimension.

marcus_sommer — Wed, 10 Feb 2016 08:00:59 GMT

You could use a calculated dimension like these here:

=if(Week <= vMaxWeek, dual('Week - ' & pick(Week, '1st','2nd','3rd','4th','5th','6th'), Week))

- Marcus

Re: Calculated Week Dimension.

renuka_sasikumar — Wed, 10 Feb 2016 08:46:31 GMT

Hi,

Thanks for your prompt reply.

I tried this but I get the output as Week - 5th & Week - 6th.

But I want Week 5 to be renamed as Week - 1st instead of Week - 5th.

Regards,

Renuka S

Re: Calculated Week Dimension.

marcus_sommer — Wed, 10 Feb 2016 08:53:57 GMT

Then you need to reverse the order within the pick-function and/or change the sort-order of these dimension within the sort tab.

- Marcus

Re: Calculated Week Dimension.

renuka_sasikumar — Fri, 12 Feb 2016 09:28:44 GMT

Thank You. This worked out. 🙂