Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I'm looking for some help !
I have a database with 2 dates (expedition date and reception date) by row. I would like to have a table with the total weight received by expedition month but with a display by reception month.
I tried :
Col1 : Monthname(ReceptionDate)
Col 2 : {1<[ExpeditionDate]=p([ExpeditionDate])>}Sum(Total<[ExpeditionDate], [Lot]>[Weight])
But this print the global Weight by fiscal year.
Thanks for your help !
An
Hello @Lou_Lab
Given that you want a table with the total weight received by "expedition month" but displayed by "reception month," here's how you can set it up:
MonthName(ReceptionDate)
as you've stated.To achieve the desired result, you might consider the following approach:
1. Dimension:
Monthname(ReceptionDate)
1. Expression:
Sum({$<ExpeditionDate = {"=$(=MonthStart(ReceptionDate))"}>} Weight)
Here's a breakdown:
Monthname(ReceptionDate)
will give you the month-year representation of the ReceptionDate
.
The Set Analysis expression, {...}
, allows you to conditionally sum your data based on certain criteria:
$<...>
makes sure your current selections in other fields are still considered.
ExpeditionDate = {"=$(=MonthStart(ReceptionDate))"}
restricts the data set to records where the ExpeditionDate
falls within the month of the ReceptionDate
in the current row of your table.
With this setup, for each row in your table (which represents a ReceptionDate
month-year), the measure will give you the sum of Weight
for records where the ExpeditionDate
falls within that same month-year.
Hi @Mike_Dickson are you sure about this ? because it return nothing for me 😕
Thanks !