Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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