Getting sum of goods for the first date of each month

[chetverikov]

Hi!

Supposing, I have data as following:

Test:
LOAD Date(%Date)as %Date, %Month, %GoodId, %Count
INLINE [
%Date, %Month, %GoodId, %Count
30.01.2022, January, 1, 1
30.01.2022, January, 2, 2
31.01.2022, January, 1, 4
31.01.2022, January, 2, 8
01.02.2022, February, 1, 16
01.02.2022, February, 2, 32
02.02.2022, February, 1, 64
02.02.2022, February, 2, 128
]
;

I am trying to find the total count of goods (for both good 1 and 2) for a minimum date for each month. I have tried two ways to get the expected result:

1) =SUM({<%Date={"$(=Min(%Date))"}>} %Count)  - this results in count for the minimum date of all the data, not for an each month

2) =SUM({<%Date={"=Min(%Date)"}>} %Count) - and this gets total count for each month, in other words filter for min date does not work

But when I just want to find a minimum date by using =Min(%Date) , the expression gets an expected minimum date of a month

Raw data:

Results of the expressions ("30.01.2022" is evaluation of "$(=Min(%Date))"):

What I want to get:

%Month	CountOfMinDate
February	48
January	3

Can anyone please help?

Thanks in advance

UPD: I have restrictions for a solution to my problem:

1) Period of data can be changed by user arbitrarily

2) There can be several time dimesions (day, week, month, ...), which are chosen by user

[canerkan]

Hi Chetverikov,

try mapping your min Dates in your Loadscript like this to get themin date for your GoodId in each month:

MinDates:
Mapping Load
	Date(Min(%Date)) & '_' & %Month & '_' & %GoodId as minDate,
	1 as flag
Resident Test
Group by %GoodId, %Month;

And then Reload your table and apply this map like this:

Table:
Load
*,
ApplyMap('minDates', Date(%Date) & '_' & %Month & '_' %GoodId, 0) as flag
Resident....

That way you can use the following set expression in your calculation:

Sum({$<flag = {1}>}%Count) to get your result.

Let me know if it helped,

Can

[chetverikov]

Thanks for your reply, Canerkan

This does not work in my case, because data period can change arbitrarily after script loading, sorry that I have not metioned it. And if I would have several time dimensions (day, week, month, quarter, year), which could be turned on/off dynamically by user, I should keep several mapping tables and handle all the combinations of the dimensions. I want to find some solution for the problem: while aggregating data in rows of a table, take sum of goods count with a minumum date of a group. It sounds simple (for example, SQL has window functions for that), but still I am struggling. 

I have also tried: SUM(if(%Date=min(%Date),%Count,0)) and I had an attempt to save min(%Date) into another column - nothing has helped