topic Re: Previous 3 Moths Dynamic Set Analysis in App Development

Previous 3 Moths Dynamic Set Analysis

CalvinTosen — Wed, 02 Oct 2024 08:38:40 GMT

Hi Everyone.

I would appreciate any assistance with this expression.

I need to calculate the sales for each product three months before the product's last procurement date.

Sum({<Transaction_Date={">=$(=MonthStart(Max([Pro_Date]), -3))<=$(=MonthEnd(Max([Pro_Date]), -1))"}>} [LINE.NETT.VALUE] )

This works when 1 product code is selected but when multiple product codes are selected it uses the highest max(Pro_Date) for each product code.
Apologies I'm not allowed to show the sales values

Apologies I am not allowed to post the sales values.

In this case, instead of calculating three months back from 2023/05/16 for product code 490843, it's calculating the previous three months from 2024/09/04 for both of them.

If anyone has any solutions it would be greatly appreciated and if any more information is required please let me know, I can provide dummy data if its required.

Re: Previous 3 Moths Dynamic Set Analysis

Kushal_Chawda — Wed, 02 Oct 2024 09:28:23 GMT

@CalvinTosen Set analysis works once per chart. It doesn't calculated row by row. Hence, it will not consider max values for individual product but max from all product. Try below expression

sum({<[LINE.NETT.VALUE] = {"=aggr(Transaction_Date >= monthstart(max(total <Product>[Pro_Date]),-3) and [Transaction_Date <= monthend(max(total <Product>[Pro_Date]) ,-1),Product, Transaction_Date)"}>}[LINE.NETT.VALUE])

Re: Previous 3 Moths Dynamic Set Analysis

CalvinTosen — Wed, 02 Oct 2024 10:59:42 GMT

Hi Kushal

Thank you so much for replying.

Unfortunatly is does not seem to be working, it is giving 0 values.

Re: Previous 3 Moths Dynamic Set Analysis

Kushal_Chawda — Wed, 02 Oct 2024 11:02:27 GMT

@CalvinTosen make sure that you are using correct field names for Sales, Product and Dates. Is your measure giving any error? Please share screenshot of your measure

Re: Previous 3 Moths Dynamic Set Analysis

CalvinTosen — Wed, 02 Oct 2024 11:24:22 GMT

Field Names are [Product Code no Branch] for the products

[LINE.NETT.VALUE] for the Sales

Transaction_Date for the Sales Dates

Pro_Date for the procurement date
Original measure
Sum({<Transaction_Date={">=$(=MonthStart(Max([Pro_Date]), -3))<=$(=MonthEnd(Max([Pro_Date]), -1))"}>} [LINE.NETT.VALUE] )

Proposed solution with field name changes
sum({<[LINE.NETT.VALUE] = {'=aggr(Transaction_Date >= monthstart(max(total <[Product Code No Branch]>[Pro_Date]),-3) and [Transaction_Date <= monthend(max(total <[Product Code No Branch]>[Pro_Date]) ,-1),[Product Code No Branch], Transaction_Date)'}>}[LINE.NETT.VALUE])

The proposed solutionThe original

Let me know if you require anything else

Re: Previous 3 Moths Dynamic Set Analysis

Kushal_Chawda — Wed, 02 Oct 2024 11:31:57 GMT

@CalvinTosen It seems you are using procurement date in dimension so you might need to add that as well in aggr function. If it doesn't work please share sample data with expected output

Re: Previous 3 Moths Dynamic Set Analysis

CalvinTosen — Wed, 02 Oct 2024 12:13:21 GMT

My apologies for taking so long Kushal I quickly made a dummy app.
I marked where it is currently summing the LINE.NETT.AMOUNT for both product codes since 490930's latest Pro_Date is in September and I marked in Green the values that 490843 is supposed to use. Hope this Helps

Re: Previous 3 Moths Dynamic Set Analysis

Kushal_Chawda — Wed, 02 Oct 2024 12:57:04 GMT

@CalvinTosen Can you share demo app? Without looking at data model it will be hard to say what's wrong

Re: Previous 3 Moths Dynamic Set Analysis

CalvinTosen — Wed, 02 Oct 2024 13:02:55 GMT

Hi Kushal

I believe I found a solution this expression seems to be working:

Sum(
Aggr( nodistinct
If(
[Transaction_Date] >= MonthStart(Max(TOTAL <[Product Code No Branch]> [Pro_Date]), -3)
and [Transaction_Date] <= MonthEnd(Max(TOTAL <[Product Code No Branch]> [Pro_Date]), -1),
[LINE.NETT.VALUE]
),
[Product Code No Branch], [Transaction_Date],[DOC.NO]
)
)

Thank you so much for the assistance without your expression I would never have thought of the idea to use Aggr like this.

The [DOC.NO] is the invoice number that allows it to use duplicate [LINE.NETT.VALUE]s .