Null data repeats the above value

[paulwalker]

Hi Community,

I'm facing the one issue, Actually my data not properly.

Budget data they were given to me, Month wise. (Jan to Dec)

Have converted to date level,  my challenge is compare week wise data.

suppose WEEK1 = Jan Budget/Number of working days in a week.

Suppose My Jan data =200

=200/5 (working days)

=40

See the below image,Zero values have to fill the above value...

Note: JAN month all Weeks should be same amount

Please help me...

PFA..

[Anil_Babu_Samineni]

Can you try this in script Fiscal Year and you will get automatic.

[sunny_talwar]

May be this

Sum(Aggr(If(Date#(Month_Year, 'MMM - YYYY') = Max(TOTAL <Year, Week> Date#(Month_Year, 'MMM - YYYY')), Sum(TOTAL <Month_Year>SalesAmount)), Year, Week, Month_Year))/5

[paulwalker]

Hi Anil,

I have generated Dates, but i want repeat the value.

Suppose i have date 01-01-2018 value 100 and Weeks (Week1, week2, week3, week4).

for JAN all week value should be the same...

I have tried with SUm(Total Amount), but this giving full total of the months.

[sunny_talwar]

Or this

Sum(Aggr(If(Date#(Month_Year, 'MMM - YYYY') = Max(TOTAL <Year, Week> Date#(Month_Year, 'MMM - YYYY')), Sum(TOTAL <Month_Year>SalesAmount)), Year, Week, Month_Year))/

NetWorkDays(MIN(Common_Date), MAX(Common_Date))

[paulwalker]

Hi Sunny,

This is working fine, but if i make more than one Month selection not working properly..

IF i select JAN and FEB

taking like ( (JAN+FEB)/no of working days in a month) * No.of working days in a week

Please help

[sunny_talwar]

paulwalker‌ - can you provide the exact numbers you are looking to get with and without selection?

[paulwalker]

Sorry, Let me clear..

IF i make selection JAN and FEB, by default its taking 20 working days. (Jan working days should be 23 and Feb working days should be 20)

Jan weeks (Week 1 to 5) that should be Divide by 23 working days

Feb weeks (Week 1 to 5) that should be Divide by 20 kworking days

Hope it clear...

[paulwalker]

Hi Sunny,

Thanks for your help..

This expression working fine for me...

(Sum(Aggr(If(Date#(Month_Year, 'MMM - YYYY') = Max(TOTAL <Year, Week> Date#(Month_Year, 'MMM - YYYY')), Sum(TOTAL <Month_Year>SalesAmount)), Year, Week, Month_Year))

/

Concat(DISTINCT NetWorkDays(MonthStart(MakeDate(Year, Month)), MonthEnd(MakeDate(Year, Month))), ', '))

*

NetWorkDays(MIN(Common_Date), MAX(Common_Date))

But I'm facing one problem, I'm not able to exclude Holidays..

Please can you help on this...

HolidayDate, Name

2018-01-01, New Years Day

2018-03-30, Good Friday

2018-04-02, Easter Monday

I want to exclude these one's...

[sunny_talwar]

Why not? What issues are you running into? May be create a variable with these dates and pass it to the NetWorkDays() function