Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Ladies & Gents:
I have been reading all of the posts on if-then and set analysis and aggr with regards to all the syntax permutations and performance etc (thanks to everyone). Perhaps I am confusing the two in my attempts to get the syntax right on either! I have a combo chart with cyclical group on X-axis (Month, Week, Qtr). Pseudocode for the expression is basically the sum of Sold, for all Jobs in the current year the max date row values for Sold within each period (as there may be multiple rows per period per Job).
I have inserted my (ahem) attempts at both:
// IF statements
//sum( if(Year= (vTodaysYear), [Sold]) ) // displays but not sophisticated enough
//sum( if(Year= (vTodaysYear), if( Date= max(Date, JobID), [Sold]) ) ) // no data to display
//sum( if(Year= (vTodaysYear), if( Date= max(Date, JobID, $(C_JobSoldCalendar)), [Sold]) ) ) // no data to display
// Set analysis
//sum( {$< Year= {$(#vTodaysYear)} >} [Sold] ) // displays but not sophisticated enough
//sum( {$< Year= {$(#vTodaysYear)}, Date= {"= max(Date)"} >} [Sold] ) // displays same as above, 2nd part seems to be ignored?
sum( {$< Year= {$(#vTodaysYear)}, Date= {$( aggr( max(Date, JobID)))} >} [Sold] ) // no data to display
I imagine there is probably some fundamental flaws in my approach! Below is a test snippet of data.
JobID | Date | Month | Week | Year | Sold |
960362142 | 8/31/2009 | 8 | 36 | 2009 | 43 |
960362142 | 8/28/2009 | 8 | 35 | 2009 | 73 |
140578565 | 8/17/2009 | 8 | 34 | 2009 | 293 |
960362142 | 8/1/2009 | 8 | 31 | 2009 | 73 |
960362142 | 7/30/2009 | 7 | 31 | 2009 | 95 |
140578565 | 7/1/2009 | 7 | 27 | 2009 | 81 |
960362142 | 7/1/2009 | 7 | 27 | 2009 | 137 |
140578565 | 8/15/2008 | 8 | 33 | 2008 | 305 |
140578565 | 8/1/2008 | 8 | 31 | 2008 | 50 |
960362142 | 8/1/2008 | 8 | 31 | 2008 | 129 |
140578565 | 7/25/2008 | 7 | 30 | 2008 | 64 |
140578565 | 7/14/2008 | 7 | 29 | 2008 | 62 |
140578565 | 7/1/2008 | 7 | 27 | 2008 | 78 |
960362142 | 7/1/2008 | 7 | 27 | 2008 | 150 |
An aditional wrinkle beyond my initial struggles is that in some shorter periods, eg. week, there may be no avaible row so the last known Sold values should be included. I'd appreciate any advice.
Sincerely - Robt
Robt,
you are pretty close in most of your attempts, with a few syntax issues. For example:
Max(Date, JobID) is invalid syntax. You can use sum(total <JobID> Date) tto get the maximum date for the JobID.
Similarly, aggr(Max(Date, JobID)) is invalid. Correct syntax is aggr(Max(Date), JobID)
Within the Set Analysis expression, you can use calculations like max(date), but only within a $-sign expansion:
$(=max(Date))
Another option for you to pick the latest value is using a function FirstSortedValue() - that's perhaps the most elegant way...
cheers,
Oleg
Robt,
you are pretty close in most of your attempts, with a few syntax issues. For example:
Max(Date, JobID) is invalid syntax. You can use sum(total <JobID> Date) tto get the maximum date for the JobID.
Similarly, aggr(Max(Date, JobID)) is invalid. Correct syntax is aggr(Max(Date), JobID)
Within the Set Analysis expression, you can use calculations like max(date), but only within a $-sign expansion:
$(=max(Date))
Another option for you to pick the latest value is using a function FirstSortedValue() - that's perhaps the most elegant way...
cheers,
Oleg
Thanks Oleg. Strangely, the 'Error in expression' message was not showing for each of the example attempts so I thought that maybe the syntax was somehow partially good despite the graph not showing? I did look at FirstSortedValue() but those attempts probably suffered the same issues as you already highlighted and were nuked before the final C&P. Looking forward to making the mods and verifying.
Cheers - Robt
Oleg:
So with your help I've progessed to the following example which displays correctly in the chart (also taking into account a cyclic dimension and flags):
sum( if( SoldDaysDate= aggr( nodistinct max( SoldDaysDate), JobID, $(= getcurrentfield([C_JobSoldCalendar]))), JSDcyF*[TotalSold]) ) // finally done !!!!!
Cheers - Robt