8 Replies Latest reply: Mar 23, 2013 10:43 AM by Alessandro Pisano

# aggregations on the dates do not work as I would like to

Hi,

I have a data mart that freezes the sales situation every day.

For example, this is the content of the facts of the customer "Vitrociset Spa" (I left out the other dimensions are not interesting here):

 DT_ANALISI_PORTAFOGLIO_KEY V_EURO_PORTAFOGLIO_LORDO 41354 1 41354 1410 41354 1700 41354 3000 41354 6900 41354 8400 41354 9312,2 41354 10031,62 41354 23367,9 41354 28990,22 41354 33078 41354 33301,6 41354 33806 41355 30000 41320 20000 41323 220000

(Note that the date is expressed in numeric format)

a simple pivot for customer / date with the total sales gives this result

 Cliente DT_ANALISI_PORTAFOGLIO_KEY DT_ANALISI_PORTAFOGLIO =SUM(V_EURO_PORTAFOGLIO_LORDO) Vitrociset Spa 41354 21/03/2013 193.299 Vitrociset Spa 41355 22/03/2013 30.000 Vitrociset Spa 41320 15/02/2013 20.000 Vitrociset Spa 41323 18/02/2013 220.000

(for clarity I also added the date in date-format)

I would make the total sales per customer / month, using only data from the last day of the relevant month in a selected year.

I selected the year 2013.
I have available the dimension DT_ANALISI_PORTAFOGLIO_MESE (month of the date).

in a pivot for customer / month I calculated the expression MAX_DT_ON_MONTH as

=Max({DT_ANALISI_PORTAFOGLIO_MESE} DT_ANALISI_PORTAFOGLIO_KEY)

and I get the correct result

 Cliente DT_ANALISI_PORTAFOGLIO_MESE MAX_DT_ON_MONTH Vitrociset Spa mar 41355 Vitrociset Spa feb 41323

I applied this espression in the aggregate expression on the value V_EURO_PORTAFOGLIO_LORDO) as

=sum({\$ <DT_ANALISI_PORTAFOGLIO_KEY ={\$(=Max({DT_ANALISI_PORTAFOGLIO_MESE} DT_ANALISI_PORTAFOGLIO_KEY))}>} V_EURO_PORTAFOGLIO_LORDO)

But I can not get what I want. This is the result:

 Cliente DT_ANALISI_PORTAFOGLIO_MESE MAX_DT_ON_MONTH PORTAFOGLIO_MAX_DT_ON_MONTH Vitrociset Spa mar 41355 30000 Vitrociset Spa feb 41323 0

The first value is correct (30.000); but the second value is missing: the value on "feb" is 220.000 (the sum of the rows with DT_ANALISI_PORTAFOGLIO_KEY equal to 41323).

What am I doing wrong?

• ###### Re: aggregations on the dates do not work as I would like to

Hi,

Is it possible for you to upload qvw file with some data.

Regards,

Kaushik Solanki

• ###### Re: aggregations on the dates do not work as I would like to

See attached qvw

• ###### Re: aggregations on the dates do not work as I would like to

Thanks, it works as I wanted!

I see your formulas, but I do not understand ...
Maybe I need to learn how to use the function "aggr".
You could tell me something to read to begin to understand?

But what is the reason why my way was wrong?

• ###### Re: aggregations on the dates do not work as I would like to

See this blog post about aggr. The reason your set analysis expression doesn't work is that the set is calculated one time for the entire chart, not per row. So it can find only one max date, not a max date per month.

• ###### Re: aggregations on the dates do not work as I would like to

Thank you very much, the article is very useful and explanatory. I will study it!

• ###### Re: aggregations on the dates do not work as I would like to

I further analyzed your example. It does not do what I want.
The problem is as follows: If a value does not exist for a client to date "x" value is displayed at the time "x-1". This is not good.
I would like to:
1. define the most recent date for the month of analysis (for example, if I have three analyzes in the month of March, 01, 08 and 15, you choose the date of March 15)
2. see all and only the values at that date (in the example cited above, March 15). If a customer has NO values at that date, must be seen zero (or blank), I do not have to display values that had March 8 or March 1;
For this reason, I wanted to first determine which was the most recent date for the month in question and then display all and only the values at that date.

• ###### Re: aggregations on the dates do not work as I would like to

Hi,

I think I've solved the problem.

I created a computed dimension that determines the MaxData with aggr function, with the same dimensions of the analysis:

Aggr(

Date(Max(DT_ANALISI_PORTAFOGLIO_KEY)),

D_CLIENTE,

DT_ANALISI_PORTAFOGLIO_ANNO,

DT_ANALISI_PORTAFOGLIO_MESE

)

Subsequently I put in relation with the calculated expression

Aggr(

Sum(V_EURO_PORTAFOGLIO_LORDO),

D_CLIENTE,

DT_ANALISI_PORTAFOGLIO_ANNO,

DT_ANALISI_PORTAFOGLIO_MESE,

DT_ANALISI_PORTAFOGLIO_KEY

)

In this way, the computed dimension is constrained to only date values in the dimension DT_ANALISI_PORTAFOGLIO_KEY equal to maxdata.

See the pivot analysis named “Portafoglio mensile per cliente” in this datamart: http://files.mentelibera.it/htdocs/dm_portafoglio.qvw

I could not understand why your example shows only a customer…

Thanks for the tips on aggr ()

--

Alessandro

Da: Gysbert Wassenaar qcwebmaster@qlik.com

Inviato: venerdì 22 marzo 2013 13:08

A: palex1961

Oggetto: - Re: aggregations on the dates do not work as I would like to

<http://community.qlik.com/index.jspa> QlikCommunity

Re: aggregations on the dates do not work as I would like to

created by Gysbert Wassenaar <http://community.qlik.com/people/gwassenaar>  in Development (QlikView Desktop) - View the full discussion <http://community.qlik.com/message/325363#325363>