top of an aggregation in straight table

[sarahshong]

I have a straight table with multiple dimensions. I want to grab the top value (first of the month) for each product and month. I have an expression that has the price per day for a product. 

I want to see if I can aggregate  the expression top(TOTAL column(1)) by product and month. The result would be for each daily day, it would show the first price for the month for that product.

Any suggestions?

[sunny_talwar]

@sarahshong not entirely sure I understand what you are looking to do. Is there a sample you can share and the expected output based on the sample provided?

[edwin · Accepted solution]

try this:

in your table, add MONTH field which is monthstart(DATE)
in your chart, dimensions:
          PROD, 
          =if(date(aggr(nodistinct min(DATE),PROD, MONTH)) = DATE, DATE)  //this is calculated dim; set this as SUPPRESS WHEN VALUE IS NULL
in your expression :
          =sum(AMOUNT)  //your measure

date(aggr(nodistinct min(DATE),PROD, MONTH)) is the first of the month for each prod/month combination
when this is equal to DATE, show DATE, else NULL and the row will be suppressed