Calculated Week Dimension.

[renuka_sasikumar]

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

[MK_QSL]

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

[avinashelite]

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

[asgardd2 · Accepted solution]

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.

[marcus_sommer]

You could use a calculated dimension like these here:

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

- Marcus

[renuka_sasikumar]

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

[marcus_sommer]

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

[renuka_sasikumar]

Thank You. This worked out. 🙂