6 Replies Latest reply on Sep 17, 2018 9:30 AM by Shruthi B S

Hide last value of Calculated field from view

Hi Everyone,

Could someone please let me know a solution to hide last value of calculated field Growth .

Here I'm calculating Week/Week (W/W)Growth btw 2017 & 2018 Bookings. 2017 has all Weeks bookings. 2018 has bookings till latest week. Say 2018 has only till WK20.

To calculate W/W = (SUM([2018 Bookings])-SUM([2017 Bookings]))/SUM([2017 Bookings])

But i do not want to display Growth for WK20 because it is still not completed in 2018. Growth chart should stop at WK19, but bookings should be there. Is there a way to achieve this.

• 1. Re: Hide last value of Calculated field from view

Hi Shruthi,

You could wrap a test around your Growth calculation. See the 'Growth with Offset' field and extra column on the attached.

IF INT(DatePart('week',Today())) > (INT(Right(ATTR([Week Name]),2)) + [Week Offset])

THEN (SUM([2018 Bookings])-SUM([2017 Bookings]))/SUM([2017 Bookings])

ELSE NULL

END

I added a 'Week offset' parameter (see Parameter control set to 18 on the right hand side) to make your week 20 match to Week 38, as it appears that mid-September is 38 weeks into 2018.

Thanks,

Jonathan

• 2. Re: Hide last value of Calculated field from view

Hi Jonathan,

Thanks for your response. Could you please attach a copy of 10.4 version workbook for reference.

Also, in my actual project, data source is live a gets updated everyday. I cannot use a parameter. Any other way apart from parameter option.  I'm referring to Fiscal Week rather than Calendar week.

Thanks,

Shruthi

• 3. Re: Hide last value of Calculated field from view

Sruthi,

Can you try this?

(SUM([if [Week Name]={Fixed [Year]=2018: Max([Week Name])} then 0 else 2018 Bookings])-SUM([2017 Bookings]))/SUM([2017 Bookings])

• 4. Re: Hide last value of Calculated field from view

Hi Maneesh,

This will always return -100% for the last value.

• 5. Re: Hide last value of Calculated field from view

You are correct. I slightly changed the formula

IF MAX({ FIXED : MAX(IF [Year]=2018 then [Week Name] END)})=ATTR([Week Name]) then NULL

ELSE

[Growth]

END

1 of 1 people found this helpful
• 6. Re: Hide last value of Calculated field from view

Thank You. It worked as expected.