Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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
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?
Hi,
let's go throught each part of 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.
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