Custom label based on another field.

[Bashar]

Hello, I have a requirement to show the Ordered Units in the last 3 weeks in a table (each in a column of its own) based on (the selected Year and selected Week) or just (the selected YearWeek)

For the value, I had to create a counter for the Year Week in my source which counts the number of year weeks from start to end, so I end up with about 400 weeks based on our dataset, this is so I can show the last week when the user selected something like '2018 01', I used this expression for the 3 columns, just changing the deduction in the nested set analysis, and it works.

=Sum( {$<

[Fiscal Year Week] = P({1<[Fiscal Year Week Sort]={'$(=$(vMaxYearWeekSort) - 1)'}>} [Fiscal Year Week]),
[Fiscal Week]           = P({1<[Fiscal Year Week Sort]={'$(=$(vMaxYearWeekSort) - 1)'}>} [Fiscal Week]),
[Fiscal Year]              = P({1<[Fiscal Year Week Sort]={'$(=$(vMaxYearWeekSort) - 1)'}>} [Fiscal Year])

>}[Ordered Units] )

The problem is the label, how can I show the label to be based on the vMaxYearWeekSort field?

I used this in the label of the first column, but it doesn't work in the scenario ('2018 01') I mentioned above.

='WK ' & Right('$(=$(vSelectedFiscalYear))',2)& '-' & '$(=$(vSelectedFiscalWeek) - 1)'

When selecting Year Week '2017 31' - Works

When selecting Year Week '2018 01' - Does not work

It breaks because saying -1 doesn't make sense with this Year Week Format, which is why I want to create this custom label based on MaxYearWeekSort -1.

Appreciate any help.

[G3S]

using 'if' would work. if week selected  is week 1, then the '0' week should be wk 52 (needs to be specified),

if other weeks are selected (like 31 etc), then your calculation mentioned. 

same problem as wk 1 would occur for wk 2 as well. 

[Rohan]

Hi Bashar,

Can you share the expressions for these varaibles ? We can tweek them accordingly to get your desired output, without any 'if' so that your performance is also not hampered.

Regards,

Rohan.

[Bashar]

Hi Rohan,

these are the variables:

first one is the counter field.

vMaxYearWeekSort = Max([Fiscal Year Week Sort])

And these two are to get the max week in the max year, of course by default those columns are hidden and only appear if the user selects one.

vSelectedFiscalYear = Max([Fiscal Year])

vSelectedFiscalWeek = Max({<[Year]={"$(vSelectedFiscalYear)"}>} [Fiscal Week])

[Bashar]

I get you, but I made that YearWeekSort field for this reason, so I wouldn't have to deal with the actual week values at the beginning or end of the year, and instead control it through this YearWeekSort field.

Technically those columns wont even appear unless the user makes a selection of Year AND Week, OR just YearWeek.

[Rohan · Accepted solution]

Hi Bashar,

Instead try these variables,

vSelectedFiscalYear=year(yearstart(max(Date),0,4));

vSelectedFiscalWeek=week(Weekstart(max(Date),$1));

then in your labels, you will call these as :

Current Week :  'WK ' & Right('$(=$(vSelectedFiscalYear))',2)& '-' & '$(vSelectedFiscalWeek(0))';

T-1 Week : 'WK ' & Right('$(=$(vSelectedFiscalYear))',2)& '-' & '$(vSelectedFiscalWeek(-1))';

T-2 Week : 'WK ' & Right('$(=$(vSelectedFiscalYear))',2)& '-' & '$(vSelectedFiscalWeek(-2))';

Here, we are using parameterized variable. Try this out once & check if it works for you. & since we have switched the calculation part on max(date) instead of year or week field; that breaking of labels in the 1st couple of weeks of the year selections, wont happen.

Note : Adjust the parameters for yearstart & weekstart according to your fiscal preferences.

Regards,

Rohan.

[Bashar]

Hi Rohan,

Thanks for your reply.

I couldn't get the parameter to work, it kept coming up blank with an error, so I just put the labels like this without variables, and the year wasn't reacting, so i had to do this.

Last Week ='WK ' &Right(year(yearstart(WeekStart(max([Retail Date]),-1))),2) &'-'& Week(WeekStart(max([Retail Date]),-1))

2 weeks    ='WK ' &Right(year(yearstart(WeekStart(max([Retail Date]),-2))),2) &'-'& Week(WeekStart(max([Retail Date]),-2))

3 weeks    ='WK ' &Right(year(yearstart(WeekStart(max([Retail Date]),-3))),2) &'-'& Week(WeekStart(max([Retail Date]),-3))

It's working now! When selecting 2018 - 02

Thanks for the help!