SUMIFS in Datameer

Follow

Jesu Joseph

• November 07, 2016 19:27

What is the best way to do a classic excel sumifs in Datameer?

For example, I have 4 columns: Customer Name, Product, Purchase Year and Sales Price. I created a GROUPBY column of Customer Names. Now I want a column which adds up Sales Price of all Product "A" purchases in Purchase Year of "B". 

How do I best do that?

0

• 

  Grant Eaton

  • November 07, 2016 19:40
  • Edited

  Hi Jesu,

  If I understand your question correctly, I think you should be able to create a "Prep" worksheet with two columns. One column in that worksheet will contain a conditional formula that indicates the Sale Price IF the Purchase Year = 2016 otherwise 0; and the other column will do the same but where the Purchase Year = 2015. Note that the constants of 2016 and 2015 could be exchanged for dynamically formatted year variables derived by using the TODAY function coupled with YEAR.

  In the simplest method, your first column would look something like this:

  IF(Year(#RawDataWorksheet!PurchaseDate==2015;#RawDataWorksheet!SalesPrice;0)

  Your second column would look like:

  IF(Year(#RawDataWorksheet!PurchaseDate==2016;#RawDataWorksheet!SalesPrice;0)

  Then, you would GroupBy() CustomerName in your results worksheet and GroupSum the two conditional columns from your Prep worksheet. You can then make it more sophisticated by making the date test dynamic instead of static.

  Did I understand your question correctly?

   

  1

  Comment actions Permalink

Please sign in to leave a comment.