2 Replies Latest reply: Mar 7, 2018 2:14 AM by max payne

# Sum values of max date of period

Hello,

I need to develop a measure in Qlik Sense that sums the product quantities of the last day of the period. Here goes some explanation of the measure and visualization:

• The period of visualization can change, it can be: Year, Month, Quarter, Week, Day ...
• The database has the information of quantity of products per day

Here goes some examples:

Database:

 Date (MM/DD/YY) Product Quantity 01/01/18 A 10 01/01/18 B 20 01/01/18 C 30 01/15/18 A 5 01/15/18 B 50 01/15/18 C 100 01/15/18 D 80 02/01/18 A 5 02/01/18 B 15 02/01/18 C 20 02/08/18 A 15 02/08/18 B 30 02/08/18 C 30 02/08/18 D 50

So lets see some visualizations that I would like:

Visualization 1:

 Date (MM/DD/YY) Quantity 01/01/18 60 01/15/18 235 02/01/18 55 02/08/18 110

Visualization 2:

 Month Quantity Jan 235 Feb 110

Visualization 3: (This one uses the max date, in this case 02/08/2018)

 Product Quantity A 0 B 30 C 30 D 50

Visualization 4:

 Month Product Quantity Jan A 5 Jan B 50 Jan C 100 Jan D 80 Feb A 0 Feb B 30 Feb C 30 Feb D 50

I think it is not a good idea to use AGGR, because the database is very big and there are more information about the products that can be put in the visualization. Can anyone help me?

Daniel

• ###### Re: Sum values of max date of period

Hi,

Create Year, Month,quarter, week ,day field in script based on date field

then

for 1:

dimension : Date

Expression : Sum(Quantity)

for 2:

dimension : Month

Expression : Sum(Quantity)

for 3:

dimension : product

Expression : Sum({<Date={"=\$(=max(Date))"}>}Quantity)

for 4:

dimension : Month, Product

Expression : Sum(Quantity)

Regards,