Calculating sum of cost for dynamic weeks

Question & Answer
,
1

Hi Friends,
I am generating two weeks(current and previous) based on the user selected date. e.g.
When user selects 28th june, 28th june becomes the current week’s end date.
(current week - 22-28 june)
(previous week -15-21 june.)

When user selects 18th june, 18th june becomes the current week’s end date.
(current week - 12-18 june)
(previous week -5-11 june.)

my calculated field to get this custom weeks is-

addDateTime(extract('WD', ${SelectEndDate}), 'DD', truncDate('WK', {line_item_usage_date}))

now I would like to add sum of cost for each custom week and display that cost under each column of week, this is where I am stuck.

I tried to calculate it using this calculated field-

ifelse(
    {line_item_usage_date} >= {cal_week_start_test}  AND 
    {line_item_usage_date} <= {cal_week_end_test}, 
    {line_item_unblended_cost}, 
    0
)

but unfortunately it does not calculate the correct value. moreover when I add columns as week_start_date the value is shown for a single day.

in the screenshot below for the week 22-28 june the expeced values are shown for 22 june rather than sum of cost for (22-28 june)

Screenshot 2024-06-29 at 7.55.13 PM
Screenshot 2024-06-29 at 7.55.13 PM1552×976 115 KB

Could you please help , how can we achieve the correct sum for custom weeks?

Thank you

2

Hi @Amit_Raj ,

I could not follow the calculation done to fetch the weeks. Let me share how I plan to execute this.

Considering you are using a date parameter inputdate for capturing the date, I would create a flag to identify current week and previous week and use it in pivot table.

Step 1: Create parameter with date type named inputdate
step 2 : Create Week Identifier conditional if the date range lies between inputdate -13 days and inputdate-7 days then return previous week date range text, else if date range lies between inputdate-6 days and inputdate then return current week date range text, else return remaining days.
Step 3: Create a pivot table with Week identifier in columns and sum(line_item_unblended_cost) as values and account name as rows.
Step 4 : Add a filter to exclude remaining days category from Week identifier.

If this does not work, would suggest you to share sample data in Arena for a quick preview.

Thanks,
Prantika

1 Like
3

Hi @prantika_sinha
Thank you so much for replying on a weekend.
I followed the steps and I was able to do it. I exactly followed the steps you mentioned.
Thank you so much!

1 Like