Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Creating new table

Hi

 

I have the following challenge.  I have a table that contains customer orders in the form

 

<customer>, <order_value>, <time_stamp>

 

I want to create a new table that would contain

 

<customer>, <cumulative_orders_value>, <end_of_month>

 

the above table shall have

  • for every month from the earliest order even placed, till the present date month , the last date of the month. This would be the <end_of_month> value
  • the cumulative sales for the said customer of all orders ever placed up until and including the <end_of_month> period

thank you in advance

 

BR

 

Chronis

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    PattemManohar wow

     

    this looks a lot more complicated that I expected !!!! thank you very much indeed. !!!

     

    my only concearn is that the code seems to have the customer names "a" and "b" hardwired in the code. In the actual case there will be hundreds of customers.

     

     

5 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Anonymous Please post the sample data and expected output which will help to resolve the scenario quicker...

    • bethallcot's avatar
      bethallcot
      New Member
      7 years ago

      Thank you for sharing this information, because I had the same problem at Eduzaurus.com. This tip was really helpful.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      PattemManohar lets assume we have the following data

       

      the 3 columns on the left are sales from customes a and b

       

      customer a places one order in Jan and one order in March

      customer b places one order in Feb and one in april

       

      in the output table we want for each month between Jan and March, for each customer, the the total number of sales up until the end of the month of the row.

       

      so given that we have 2 customers, for every month there will be 2 rows (one for each customer)

       

      I hope this clarifies the problem


      • PattemManohar's avatar
        PattemManohar
        Community Champion
        7 years ago

        Anonymous Thanks for providing the sample data and detailed explanation.

         

        Here is the steps I've followed to achieve it

         

        Step1 : Added new field in the source table as below

         

        EOMonth = EOMONTH([Date],0)

        Step2 : Created new table as below

         

        CustVolOut = 
VAR _Temp = SELECTCOLUMNS(CustVol,"Customer",[Customer],"Volume",[Volume],"Dat",EOMONTH(CustVol[Date],0))
VAR _MinDate = MIN(CustVol[Date])
VAR _MaxDate = EOMONTH(MAX(CustVol[Date]),0)
VAR _Calender = FILTER(CALENDAR(_MinDate,_MaxDate),[Date]=EOMONTH([Date],0))
VAR _CrossJoin = CROSSJOIN(_Calender,_Temp)
VAR _Res = FILTER(ADDCOLUMNS(_CrossJoin,"Flag",LOOKUPVALUE(CustVol[Customer],CustVol[Customer],[Customer],CustVol[EOMonth],[Date])),[Flag]<>BLANK())
VAR _Res1 = ADDCOLUMNS(_Res,"Flag1",IF([Date]<>[Dat],"b","a"))
VAR _Res2 = ADDCOLUMNS(_Res1,"Volume1",IF([Flag]=[Flag1],[Volume],0))
VAR _Res3 = SELECTCOLUMNS(_Res2,"Customer",[Flag1],"Sales",[Volume1],"Period Ending",[Dat])
RETURN _Res3

        Step3 : Add a new field in the table that was created in above Step2 

         

        TotalCummSales = CALCULATE(SUM(CustVolOut[Sales]),FILTER(ALL(CustVolOut),CustVolOut[Customer] = EARLIER(CustVolOut[Customer]) && CustVolOut[Period Ending] <= EARLIER(CustVolOut[Period Ending])))

        Finally, here is the output table..

         

         

        I definitely believe, there might be more efficient way of doing the same, but this is the approach that is currently on top of my head. But I'll be glad if it atleast helps you a percent....