topic Re: Aggr with data filter in Featured Members

Aggr with data filter

AdminLoghaus — Wed, 16 Oct 2024 14:07:52 GMT

I need help with this expression. The goal is to calculate within set analysis the field ProductLastEntryDateSite + 45 days forward. This calculation needs to be done for each product where the field is ProductCode. In other words, each product has a date in the field ProductLastEntryDateSite. However, in the table, the result will only be displayed based on the field ProductLastEntryDateSite. Therefore, I need to create a virtual table using the Aggr function to get this result.

The problem is that when I include the dimension ProductCode in the table, the information appears, and the result is correct. But when I remove the ProductCode dimension and leave only ProductLastEntryDateSite, the result becomes null. How can I apply date filters within the Aggr function?

Expression:

Sum({<ProductYearMonthLastEntrySite>}
Aggr({<
Date = {">=$(=Date(Max(ProductLastEntryDateSite)+0))<=$(=Date(Max(ProductLastEntryDateSite)+45))"},
Year = {">=$(=Year(Max(ProductLastEntryDateSite)+0))<=$(=Year(Max(ProductLastEntryDateSite)+45))"},
YearMonth = {">=$(=MonthName(Max(ProductLastEntryDateSite)+0))<=$(=MonthName(Max(ProductLastEntryDateSite)+45))"}>}
Sum({<
TransactionType = {'Invoicing'},
BusinessTransaction = {'E-commerce'},
ProductLastEntryDateSite = {">=$(v.cutStartDate)"}
>}
OrderItemSaleQuantityInvoiced),ProductYearMonthLastEntrySite))

Re: Aggr with data filter

Kushal_Chawda — Wed, 16 Oct 2024 14:36:57 GMT

@AdminLoghaus I think you don't need set for Year & YearMonth as you only required it for next 45 days

Sum({<Date = {">=$(=Date(Max(ProductLastEntryDateSite)+0))<=$(=Date(Max(ProductLastEntryDateSite)+45))"}>} Aggr( Sum({< TransactionType = {'Invoicing'}, BusinessTransaction = {'E-commerce'}, ProductLastEntryDateSite = {">=$(v.cutStartDate)"} >}OrderItemSaleQuantityInvoiced), ProductYearMonthLastEntrySite))

Re: Aggr with data filter

AdminLoghaus — Wed, 16 Oct 2024 16:23:27 GMT

Right. I used your suggested measure, but the result is larger than it should be. My issue is applying the filter within the set analysis or through aggregation so that it applies the 45-day date filter for each ProductCode that I have in the structure as a dimension. In other words, when I add the ProductCode dimension to the table, the result is accurate. However, I can't keep this dimension in the table because my indicator needs to be opened only by Date, and the measure I'm trying to create uses aggregation functions. So, something is missing to filter the date range at the level of each ProductCode.

Thank you in advance for your help.

Re: Aggr with data filter

AdminLoghaus — Wed, 16 Oct 2024 16:57:11 GMT

Complementing my difficulty. Where in this measurement do I add the filter for the ProductCode dimension? I tried this way in the image, but it doesn't work.

Re: Aggr with data filter

Kushal_Chawda — Wed, 16 Oct 2024 18:42:22 GMT

@AdminLoghaus if you want your each product code to be within 45 days independently then set analysis won't work. You will need if condition

=sum({< TransactionType = {'Invoicing'}, BusinessTransaction = {'E-commerce'}, ProductLastEntryDateSite = {">=$(v.cutStartDate)"} >} aggr(if(ProductLastEntryDateSite >= max(total <ProductCode> ProductLastEntryDateSite) and ProductLastEntryDateSite <= max(total <ProductCode>)+45,OrderItemSaleQuantityInvoiced),ProductCode,ProductLastEntryDateSite,ProductYearMonthLastEntrySite))

Re: Aggr with data filter

AdminLoghaus — Wed, 16 Oct 2024 20:07:16 GMT

I used your measure, but it didn’t achieve the desired result. Even so, I took your measure and adjusted it for the situation I need.

The field Data in the script structure belongs to the same table as the field ItemOrderSaleQuantityInvoiced, so what I actually need is for the Data field to be equal to ProductLastEntryDateSite, and also for the Data field to equal ProductLastEntryDateSite + 45, in order to get the correct result.

In my simple table, where I'm showing my indicator, it should only display based on the field ProductYearMonthLastEntryDateSite and the measure we are trying to build. I appreciate the help once again.

Sum({<ProductYearMonthLastEntryDateSite>} Aggr( sum({< TransactionType = {'Invoicing'}, BusinessTransaction = {'E-commerce'}, ProductLastEntryDateSite = {">=$(v.cutStartDate)"} >} aggr(if(Data >= Max(<ProductCode>ProductLastEntryDateSite) and Data <= Max(<ProductCode>ProductLastEntryDateSite) + 45, ItemOrderSaleQuantityInvoiced), ProductCode)), ProductYearMonthLastEntryDateSite))

Re: Aggr with data filter

Kushal_Chawda — Thu, 17 Oct 2024 08:43:34 GMT

@AdminLoghaus It will be hard to suggest anything without looking at data model and sample data. But idea here is that you need to understand the different level of your filter and aggregation and formulate the measure accordingly