Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Lou_Lab
Contributor
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.

Thanks for your help !

An

Labels (3)
2 Replies
Mike_Dickson
Support
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!
Lou_Lab
Contributor
Contributor
Author

Hi @Mike_Dickson are you sure about this ? because it return nothing for me 😕 

Thanks !