Good day All,
Situation: I have col product name, datetime (!), product id. Those that are ProductName2, ProductName3, and ProductName4 we need to multiply the total quantitity by 12. The product labeled ProductName1, we need to multiply by 40.
To find the cumulative total, I used the below fomula and it worked just fine.
(COUNT(SELECT(@C:C;, (@AI:AI;="ProductName1")))*40)+(COUNT(SELECT(@C:C;, OR((@AI:AI;= "ProductName2"), (@AI:AI;="ProductName3"), (@AI:AI;="ProductName4"))))*12)/1000
Now, I need to get the cumulative total by month. For X-axis, I use:
DATERANGE(MIN(DATE(@J:J;, "dd-MMM-yyyy hh:mm:ss")),TODAY())
The question is how to build a formula for Series? I know that I need to use LOOKUP, GROUP and GROUPBY. But I am a little bit confused with how to handle the calculation within GROUPBY. Could you, please, advise?
LOOKUP(&'c5fd6550-3',GROUP(DATE_CONVERT(@J:J;, "dd-MMM-yyyy hh:mm:ss", "MMM yyyy")),GROUPBY(SELECT(@C:C;, (@AI:AI;="ProductName1"))*40)+SELECT(@C:C;, OR((@AI:AI;= "ProductName2"), (@AI:AI;="ProductName3"), (@AI:AI;="ProductName4"))*12))