Announcements
cancel
Showing results for
Did you mean:
Contributor

## Set Analysis total by month on two dates

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.

An

Labels (3)

• ### Set Analysis

2 Replies
Support

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:

1. Your Dimension will be `MonthName(ReceptionDate)` as you've stated.
2. Your expression for total weight by "expedition month" with display by "reception month" is a bit complex and you need to adjust your Set Analysis.

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.

Sr. Technical Support Engineer with Qlik Support
Don't forget to mark a solution that worked for you!
Contributor
Author