Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello guys!
I am struggling to find a solution for my "monthly sales issue", hopefully you can help!
I want to create a table with monthly sales per item (and same one per customer) druring load. Something that looks like this:
Item ID | Year_Month | Total sales |
1 | 2022_1 | 0 |
1 | 2022_2 | 100 |
1 | 2022_3 | 80 |
2 | 2022_1 | 300 |
2 | 2022_2 | 250 |
2 | 2022_3 | 0 |
Before creating the load script I tried to create such a table in the app and failed. If there is no demand during a month for a given Item ID, I get a '-'. Hence, the above table has '-' instead of zeroes.
I have tried the if function, but it did not help. Neither if(sum(Sales)='-','0') nor if(sum(Sales)='-',0).
The data that I am using as a basis for calculation is a list of shipments. So it happens that for a given Item there is no shiments in a particular month...
Without zeroes as values I cannot proceed with further calculations like average monthly sales or standard deviation.
Please help.
Thanks & regards,
Sergii
Not sure where you are trying, check if you can use this function to show zeroes -
Coalesce(Sum(Sales),0)
I need to create a table with a total demand per item per months with a value, even if there is no demand for a given item - month combination according to data.
Unfortunatelly Coalesce did not hlep, I still get empty fields: