Need to calculate sales data for the month of January with the help of February data

[Sri251]

Hi All,

I need help...

I want to calculate sales data for the month of January with the help of February data. Actually from the Source data, it is coming on monthly basis for the whole year. So there is no data (Zero) given for the month of January  instead of that they have combined January and February month's data  altogether. Here I need to divide and split the half of the February data into January by week.

PFA Sheet for your reference and give me your valuable thoughts!

Thank you,

Sri

[ajaykakkar93]

Hi,

I have aggregated by month year,
try the below script.

datat:
load * Inline [
Week_numbers,Month,Sales,Expected_Result,Year
1,January,0,145000,2023
2,January,0,145000,2023
3,January,0,145000,2023
4,January,0,145000,2023
5,February,290000,145000,2023
6,February,290000,145000,2023
7,February,290000,145000,2023
8,February,290000,145000,2023
9,March,345000,345000,2023
10,March,345000,345000,2023
11,March,345000,345000,2023
12,March,345000,345000,2023
13,March,345000,345000,2023
];

data:
load
MonthName(date#(Month &' '&Year,'MMM YYYY')) as T_MonthName,
if((Sales) = 0 , Peek(Sales)/2 , Sales) as Sales
;
load
// Week_numbers,
Month,
Year,
sum(Sales) as Sales,
// Expected_Result,
124 as j
resident datat
group by Month,Year
Order by Month asc, Year
;

Drop Table datat;

[Sri251]

Thank you Ajay for your reply.

Here I am getting all the data from a data set (view/table from a database), so I am trying to split February data into January data to show the first four weeks of data(1,2,3,4 weeks) as 145000 for each week respectively for January 2023 in a line chart and also comparing this sales data with revenue data for this year (2023) 52 weeks.

FYI - I am taking week numbers as a dimension, Sales and revenue as measures in the line graph

Thank you,

Sri

[MayilVahanan]

Hi

Try like below

Temp:
LOAD * INLINE [
Week numbers, Month, Sales, Expected Result
1, January, 0, 145000
2, January, 0, 145000
3, January, 0, 145000
4, January, 0, 145000
5, February, 290000, 145000
6, February, 290000, 145000
7, February, 290000, 145000
8, February, 290000, 145000
9, March, 345000, 345000
10, March, 345000, 345000
11, March, 345000, 345000
12, March, 345000, 345000
13, March, 345000, 345000
];

join
Load [Week numbers]-4 as [Week numbers], Sales/2 as Sales1 Resident Temp where Month = 'February';

Final:
Load [Week numbers], Month, Sales, [Expected Result], if(Sales1 > 0, Sales1, if(Month = 'February', Sales/2, Sales)) as Result Resident Temp;

DROP Table Temp;

[Sri251]

Thank you MayilVahanan for your reply.

Actually i have reached out the data provider team and they are adjusting the numbers from their end for January and February 2023 as it needs to be consistent for the future data as well as this data is the planned data and it will be revised yearly 3 times. So for now i am good.

Thank you very much for your inputs.

Best regards,

Sri