Set Analysis and If-then and Aggr

[#1]

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

[Oleg_Troyansky · Accepted solution]

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

[#3]

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

[#4]

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