topic Get last occurrence lower than current month in App Development

Get last occurrence lower than current month

talita_verdes — Fri, 14 May 2021 17:09:12 GMT

Hello!

I have a question, and I don't know how to solve it:

Today I have the following data table.

Example:

SALES_DATE	PRODUCT_ID	PRODUCT_DESC	SALES_VALUE
20/04/2021	1	A	10
25/04/2021	1	A	3
13/05/2021	1	A	21
10/03/2021	2	B	11
01/02/2021	2	B	9
10/03/2021	3	C	12
11/05/2021	3	C	13

I need to show the sum of last sales by product as a measure in a table chart, where the month of sale is the last month that had a sale and it isn't in the current month.

Example:

SALES_DATE	PRODUCT_ID	PRODUCT_DESC	SALES_VALUE
01/04/2021	1	A	13
01/03/2021	2	B	11
01/03/2021	3	C	12

Could you please help me?

I tried these approach:

FIRSTSORTEDVALUE(AGGR(SUM({<SALES_DATE = {"<$(= MONTHSTART(TODAY()) )"}>} SALES_VALUE), PRODUCT_ID), -SALES_DATE)

SUM({<PRODUCT_ID = P({<SALES_DATE = {"<$(= MONTHSTART(TODAY()) )"}>} PRODUCT_ID)>} SALES_VALUE)

SUM({<SALES_DATE = {"<$(= MONTHSTART(TODAY()) )"}>} AGGR(SUM(SALES_DATE), SALES_DATE))

Thanks for your help.

Re: Get last occurrence lower than current month

rubenmarin — Sat, 15 May 2021 07:09:45 GMT

Hi, I have creted a field that stores he monthsales, becasue I think you want to accumulate by month (the 2nd table has day 1 and product A is addignboth sales of april):

LOAD *, MonthStart(SALES_DATE) as MonthSale INLINE [ SALES_DATE, PRODUCT_ID, PRODUCT_DESC, SALES_VALUE 20/04/2021, 1, A, 10 25/04/2021, 1, A, 3 13/05/2021, 1, A, 21 10/03/2021, 2, B, 11 01/02/2021, 2, B, 9 10/03/2021, 3, C, 12 11/05/2021, 3, C, 13 ];

For this data, using MonthSale and PRODCUT_ID as dimensions, this expression returns the second table:

SUM({<MonthSale = {"<$(= MONTHSTART(TODAY()) )"}>} AGGR(If(MonthSale=Max({<MonthSale = {"<$(= MONTHSTART(TODAY()) )"}>}TOTAL <PRODUCT_ID> MonthSale), SUM(SALES_VALUE)), PRODUCT_ID, MonthSale))

Re: Get last occurrence lower than current month

talita_verdes — Mon, 17 May 2021 18:04:54 GMT

Hello Rubenmarin!

Thank you very much for your help. It works.

While my question had not been answered, I founded another way to solve it, follow below:

I created a master calendar on script, with these fields:

Example:

NUM(%KEY_DATE) AS %KEY_DATE,
NUM(%KEY_DATE) AS NU_DATE,
DATE(%KEY_DATE) AS DATE,
MONTHSTART(%KEY_DATE) AS MONTH_START_DATE,
MONTH(%KEY_DATE) AS MONTH_DATE,
YEAR(%KEY_DATE) AS YEAR_DATE

Then, after that app was reloaded, I inserted this measure on table chart:

FIRSTSORTEDVALUE(
AGGR(
SUM({<MONTH_START_DATE = {"<$(=MONTHSTART(TODAY()))"}>} SALES_VALUE)
, MONTH_START_DATE, PRODUCT_ID)
, - AGGR(MAX(NU_DATE), PRODUCT_ID)
)

Anyway really thanks.