Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
talita_verdes
Partner - Contributor III
Partner - Contributor III

Get last occurrence lower than current month

Hello!

I have a question, and I don't know how to solve it:

Today I have the following data table.

Example:

SALES_DATEPRODUCT_IDPRODUCT_DESCSALES_VALUE
20/04/20211A10
25/04/20211A3
13/05/20211A21
10/03/20212B11
01/02/20212B9
10/03/20213C12
11/05/20213C13

 

I need to show the sum of last sales by product as a measure in a table chart, where the month of sale is the last month that had a sale and it isn't in the current month.

Example:

SALES_DATEPRODUCT_IDPRODUCT_DESCSALES_VALUE
01/04/20211A13
01/03/20212B11
01/03/20213C12

 

Could you please help me?

I tried these approach:

FIRSTSORTEDVALUE(AGGR(SUM({<SALES_DATE = {"<$(= MONTHSTART(TODAY()) )"}>} SALES_VALUE), PRODUCT_ID), -SALES_DATE)

SUM({<PRODUCT_ID = P({<SALES_DATE = {"<$(= MONTHSTART(TODAY()) )"}>} PRODUCT_ID)>} SALES_VALUE)

SUM({<SALES_DATE = {"<$(= MONTHSTART(TODAY()) )"}>} AGGR(SUM(SALES_DATE), SALES_DATE))

Thanks for your help.

1 Solution

Accepted Solutions
rubenmarin

Hi, I have creted a field that stores he monthsales, becasue I think you want to accumulate by month (the 2nd table has day 1 and product A is addignboth sales of april):

LOAD *, MonthStart(SALES_DATE) as MonthSale INLINE [
    SALES_DATE, PRODUCT_ID, PRODUCT_DESC, SALES_VALUE
    20/04/2021, 1, A, 10
    25/04/2021, 1, A, 3
    13/05/2021, 1, A, 21
    10/03/2021, 2, B, 11
    01/02/2021, 2, B, 9
    10/03/2021, 3, C, 12
    11/05/2021, 3, C, 13
];

For this data, using MonthSale and PRODCUT_ID as dimensions, this expression returns the second table:

SUM({<MonthSale = {"<$(= MONTHSTART(TODAY()) )"}>} AGGR(If(MonthSale=Max({<MonthSale = {"<$(= MONTHSTART(TODAY()) )"}>}TOTAL <PRODUCT_ID> MonthSale), SUM(SALES_VALUE)), PRODUCT_ID, MonthSale))

 

View solution in original post

2 Replies
rubenmarin

Hi, I have creted a field that stores he monthsales, becasue I think you want to accumulate by month (the 2nd table has day 1 and product A is addignboth sales of april):

LOAD *, MonthStart(SALES_DATE) as MonthSale INLINE [
    SALES_DATE, PRODUCT_ID, PRODUCT_DESC, SALES_VALUE
    20/04/2021, 1, A, 10
    25/04/2021, 1, A, 3
    13/05/2021, 1, A, 21
    10/03/2021, 2, B, 11
    01/02/2021, 2, B, 9
    10/03/2021, 3, C, 12
    11/05/2021, 3, C, 13
];

For this data, using MonthSale and PRODCUT_ID as dimensions, this expression returns the second table:

SUM({<MonthSale = {"<$(= MONTHSTART(TODAY()) )"}>} AGGR(If(MonthSale=Max({<MonthSale = {"<$(= MONTHSTART(TODAY()) )"}>}TOTAL <PRODUCT_ID> MonthSale), SUM(SALES_VALUE)), PRODUCT_ID, MonthSale))

 

talita_verdes
Partner - Contributor III
Partner - Contributor III
Author

Hello  Rubenmarin!

Thank you very much for your help. It works.

While my question had not been answered, I founded another way to solve it, follow below:

I created a master calendar on script, with these fields:

Example:

NUM(%KEY_DATE)                               AS %KEY_DATE,
NUM(%KEY_DATE)                               AS NU_DATE,
DATE(%KEY_DATE)                              AS DATE,
MONTHSTART(%KEY_DATE)            AS MONTH_START_DATE,
MONTH(%KEY_DATE)                         AS MONTH_DATE,
YEAR(%KEY_DATE)                               AS YEAR_DATE

Then, after that app was reloaded, I inserted this measure on table chart:

FIRSTSORTEDVALUE(
                                             AGGR(
                                                           SUM({<MONTH_START_DATE = {"<$(=MONTHSTART(TODAY()))"}>} SALES_VALUE)
                                            , MONTH_START_DATE, PRODUCT_ID)
, - AGGR(MAX(NU_DATE), PRODUCT_ID)
)

Anyway really thanks.