Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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).
Hi,
Is it possible for you to upload qvw file with some data.
Regards,
Kaushik Solanki
Yes. This is the data mart whit a subset of data.
See attached qvw
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?
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.
Thank you very much, the article is very useful and explanatory. I will study it!
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.
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
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>