Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm new to this forum so first of all Hello Everyone
Is there a way to find a maximum per expression value? Let me give an example. I have two dimensions: ClientName and TradeDate (dd/mm/yyyy). I want to find the maximum date per month per client, so per ClientName and month(TradeDate). I cannot make any changes to the database, so I cannot just map TradeDates into e.g. TradeMonths.
The outcome should look like that:
Client TradeDate MaxDateInClient&Month
ABC 10/01/2011 20/01/2011
ABC 11/01/2011 20/01/2011
ABC 20/01/2011 20/01/2011
ABC 03/02/2011 25/02/2011
ABC 04/02/2011 25/02/2011
ABC 25/02/2011 25/02/2011
Thanks in advance for any help,
Marcin
Hello Marcin,
if you don't need every single row in your table, try this:
Create a straight table with "Client" and "Month(TradeDate)" as the two dims. The Expression is "date(max(TradeDate))".
htH
Roland
Hi Roland,
Thanks for the quick reply but I actually need to have the TradeDate dimension in the table as well, so I actually need every single row, unfortunately.
Thanks,
Marcin
Hi,
Have a look at the sample file.
Hope this is what you are looking for.
Regards,
Kaushik Solanki
Hi again,
sorry, but then I think then you are in a trap.
My assumption was, that you are not able to change the datamodell. If you want to show all rows you need a field like TradeMonth. Take a look at the post of Kaushik above.
There you find the new dim and the expression similar to my second suggestion:
"Date(max( TOTAL <TradeMonth> TradeDate))"
RR
Hi Kaushik,
This would work great but I cannot make any changes to the database and have no influence on how it's "loaded" to QV, cannot edit the script and therefore I cannot create an additional dimension in the script called Month.
Thanks,
Marcin
Hi Marcin,
as you are online right now, straight forward:
It Seems you need to contact the script-writer to spend you a new field "TradeMonth". This should not be to hard for her/him.
Good Luck
Roland
Guys,
I thought it would be possible to write a formula like:
max( total <Client, Month(TradeDate)> TradeDate)
and that I just didn't know the syntax but it seems it cannot be done this way. Thanks for your time and effort.
Regards,
Marcin
Hi,
It wont work cause you need to give only field name, you cant use the calculated field.
Thats why it is much to have week as field.
Regards,
Kaushik Solanki