Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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?
Thanks in advance.
Daniel
What about this:
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,