Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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_KEYV_EURO_PORTAFOGLIO_LORDO
413541
413541410
413541700
413543000
413546900
413548400
413549312,2
4135410031,62
4135423367,9
4135428990,22
4135433078
4135433301,6
4135433806
4135530000
4132020000
41323220000

(Note that the date is expressed in numeric format)

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

ClienteDT_ANALISI_PORTAFOGLIO_KEYDT_ANALISI_PORTAFOGLIO=SUM(V_EURO_PORTAFOGLIO_LORDO)
Vitrociset Spa4135421/03/2013193.299
Vitrociset Spa4135522/03/201330.000
Vitrociset Spa4132015/02/201320.000
Vitrociset Spa4132318/02/2013220.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

ClienteDT_ANALISI_PORTAFOGLIO_MESEMAX_DT_ON_MONTH
Vitrociset Spamar41355
Vitrociset Spafeb41323

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:

ClienteDT_ANALISI_PORTAFOGLIO_MESEMAX_DT_ON_MONTHPORTAFOGLIO_MAX_DT_ON_MONTH
Vitrociset Spamar4135530000
Vitrociset Spafeb413230

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?

8 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Yes. This is the data mart whit a subset of data.

http://files.mentelibera.it/htdocs/dm_portafoglio.qvw

Gysbert_Wassenaar

See attached qvw


talk is cheap, supply exceeds demand
Not applicable
Author

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?

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

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>