Aggr function not working as expected

[luisccmm]

Hi,

I need to calculate on the clients that bought something during year 2017 in a specific store, when was the last purchased they did before that store opened 20170101.

I was thinking to use an aggregated function to solve that, so for every client_id caculate the last INVOICE_DATE before 20170101 as

aggr( max({<[INVOICE_DATE]={"<20170101"}>}[INVOICE_DATE]) ,CLIENT_ID)

And then use FUNCTION count, to count those dates (There should be just one date for every client_id in the aggregated function)

count({<CLIENT_ID = P({<[Store_number]={"1000"}, [YEAR_INVOICE]={"2017"})*P({<[INVOICE_DATE]={"<20170101"}>)

, [INVOICE_DATE]={"<20170101"}>} aggr( max({<[INVOICE_DATE]={"<20161123"}>}[INVOICE_DATE]) ,CLIENT_ID))

And put that Function on a graph, in order to count those dates (that are in fact Clients_ID) with dimension INVOICE_YEAR.

In that way I  should see how many of those clients did their previous purchase (< 20170101) in Year 2016, 2015, 2014..... In order to answer if those clients that bought in store 1000  were really active  or not.

But that Aggr FUNCTION, is not getting the results I expect, and don´t really know why? The problem is in the Aggregated function, as seems to count Distinct Invoices Date without taking into account client_id

aggr( max({<[INVOICE_DATE]={"<20170101"}>}[INVOICE_DATE]) ,CLIENT_ID)

Any Help?

Thanks in advance!!

[DavidŠtorek]

Hi,

this expression aggr( max({<[INVOICE_DATE]={"<20170101"}>}[INVOICE_DATE]) ,CLIENT_ID) returns maximal Invoice date by client ID (so you get array of values like 20161212,20161111,20161114...).

It seems that you are missing some brackets in expression ad there is something more than needed. Try this

count({<CLIENT_ID = P({<[Store_number]={"1000"}, [YEAR_INVOICE]={"2017"}>})*P({<[INVOICE_DATE]={"=aggr( max({<[INVOICE_DATE]={"<20161123"}>}[INVOICE_DATE]) ,CLIENT_ID))"}>})>}CLIENT_ID)

Hope it helps

[luisccmm]

Thanks David, but there is some misspealling in your formula, that I don´t really get.

On the other hand, I think I more or less understand what you are trying as seems that your looking for the Joint between these two sets:

- Clients ID for that particular shop.

- Maximum Dates per client ID prior That StoreOpen Day.

Nevertheless, this will probably lead to an ERROR, because if some clientes BOUGHT in different Dates along different YEARS, and some of others dates match the MAX Date of other client (2nd Set) that will count those client more than one time in different years. I am right?

[DavidŠtorek]

Hi,

let's go throught each part of expression:

1. P({<[Store_number]={"1000"}, [YEAR_INVOICE]={"2017"}>}) - this should return all CLIENT_ID which have record in YEAR_INVOICE=2017 and Store_number=1000
2. * - stands for union
3. P({<[INVOICE_DATE]={"=aggr( max({<[INVOICE_DATE]={"<20161123"}>}[INVOICE_DATE]) ,CLIENT_ID))"}>}) - this should return list of "greatest" invoice date for each CLIENT_ID which was before 20161123
4. 1*3 should together return all CLIENT_ID which meet condition that (client has record in YEAR_INVOICE=2017 and Store_number=1000) and (has some record in INVOICE_DATE befor That StoreOpen Day)
5. Than it just count remaining CLIENT_ID. You are absolutely right, that client will be counted more than one time if has more than one record in given year. If this is not required use DISTINCT in expression

Is this what you are looking for? Maybe I just don't understand your needs.Maybe it would help if you attach sample data or app.

[luisccmm]

Yess it is what I waslooking for, but there is somekind of missepealing, or error on the code, as QLIK SENSE function editor is showing an error when I pass your proposal.

It says "Error in set modifier element function set"

The following is how I translated  your proposal into local language (spanish)

count({<

ICC =

P({<[Centro]={"1243"}, [AÑO_FACTURA]={"2017"}>})

*

P({<[Fecha Facturación Pedido]={"=aggr( max({<[Fecha Facturación Pedido]={"<20161123"}>}

[Fecha Facturación Pedido]) ,ICC))"}>})

>}

DISTINCT ICC)

But point number 5) I mean if  I use time variable years in a graph with that formula, the client should be counted only once, last purchase date from today regardless of year. So if for example customer XXX1 bought items in year 2014 and 2016, it should only count that customer  in year 2016 as it´s the last purchase date. Distinct will only solve problem if that customer bought in different dates from the same year, but not in different years.

Regards