Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Sri251
Contributor II
Contributor II

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

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

Labels (1)
4 Replies
ajaykakkar93
Specialist III
Specialist III

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;

 

 

Please mark the correct replies as Solution. Regards, ARK
Profile| GitHub|YouTube|Extension|Mashup|Qlik API|Qlik NPrinting

Sri251
Contributor II
Contributor II
Author

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Sri251
Contributor II
Contributor II
Author

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