Set Analysis Syntax Issue

[samvile18]

Morning All,

I'm wondering if someone out there can help me with the following syntax;

=sum({<[Date Received]={'$(=Date([Upload Date]))'}, [Sales Channel]={'FriendsLife'},[Conversion Status]={'2 Tender Doc Issued'}, Status={'Won','Live'}>}APE)

The above works fine and returns an APE value which is brilliant. However, I need to change the two date fields to something else and as I soon as I do this it stops returning a value and gives me zero's.

The two new fields are formatted differently and I'm thinking it may have something to do with this?

The above fields are normal date fields DD/MM/YYYY the two new ones are taken from the date fields using the MonthName formula.

Can anyone please help?

[fosuzuki]

What is the syntax of the new date fields? It isn't clear to me...

Can you post a sample QVW with the working and non-working codes?

Regards,

Fernando

[samvile18]

Hi,

This is the syntax that doesn't work;

=sum({<[Date Received Month/Year]={'$(=Date([Upload Month/Year]))'}, [Sales Channel]={'FriendsLife'},[Conversion Status]={'2 Tender Doc Issued'}, Status={'Won','Live'}>}APE)

Thanks for the help

[fosuzuki]

Try this:

=sum({<[Date Received Month/Year]={'$(=Date([Upload Month/Year], 'MMMM/YYYY'))'}, [Sales Channel]={'FriendsLife'},[Conversion Status]={'2 Tender Doc Issued'}, Status={'Won','Live'}>}APE)

The idea is that the Dollar sign expansion should be replaced with a text that must be in the same format as the [Date Received Month/Year]. If you leave just Date([Upload Month/Year]), it will return the date in the default dateformat (which I guess is DD/MM/YYYY), so you have to set the correct date format (which I guess is MMMM/YYYY, the month with full name...). Please adjust the 'MMMM/YYYY' with the correct date format.

Let me know if this works or not.

Regards,
Fernando

[Anonymous]

Hi,

I think your syntax for the Set Analysis is incorrect.  your opening parenthesis should look like this {$<  not {<

Next, I would remove the date component and check that the other syntax is working.  then use listboxes containing your dates (date ranges) to get an idea of what values you are expecting.

Often when comparing dates like this you need to create a variable to hold the date you are comparing against.  in this case vUploadMonthYear = [UploadMonth/Year].  If you are trying to convert to date format on the fly I wouldn't do that here, but again throw it to the variable and test it first.  Dates can be fickle and you may be comparing DDMMYY against MMDDYY without even knowing it.

finally, the set analysis for comparing a date if the format is Month/Year might look like this

{$< [Date Received Month/Year]={'$(vUploadMonthYear)' >}

again,  check the value of [Date Received Month/Year] to ensure the same format of date.

Regards,

John.

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

If your MonthYear() function returns, say, "jan-2012" you need to specify that same date format in set analysis, if all the fields are linked properly and formats are alike

Sum({<[Date Received Month/Year]={"$(=Date([Upload Month/Year], 'MMM-YYYY'))"}, [Sales Channel]={'FriendsLife'},[Conversion Status]={'2 Tender Doc Issued'}, Status={'Won','Live'}>}APE)

This assumes the [Upload Month/Year] field is in full date format. However, if that field is already storing values in the form "jan-2012" and so that happens with [Date Received Month/Year] then you don't need the Date() function at all, rather

Sum({<[Date Received Month/Year]=P([Upload Month/Year]), [Sales Channel]={'FriendsLife'},[Conversion Status]={'2 Tender Doc Issued'}, Status={'Won','Live'}>}APE)

Where the P() function will return all possible values for that field in a given state of selections: if no values are selected, then all values are possible and so the Sum() will count on all values of it, and if you have "jan-2012" selected, then the sum for this will be shown.

Hope that helps.

Miguel

[Miguel_Angel_Baeyens]

Hi John,

Just a quick note: there is nothing wrong with "{<", actually that is the same that "{$<". The dollar sign indicates the current state of selections and is the default option, hence you don't need to specify it. Great if you do for clarification, but the set analysis will work as well.

Regards.

Miguel

[CELAMBARASAN]

Hi Sam,

     Use the expression like this

=sum({<[Date Received Month/Year]={$(=chr(39) & Date([Upload Month/Year]) & chr(39))}, [Sales Channel]={'FriendsLife'},[Conversion Status]={'2 Tender Doc Issued'}, Status={'Won','Live'}>}APE)

Hope this will help you.

Celambarasan

[Miguel_Angel_Baeyens]

Hi,

I have posted a message earlier this morning but it's being moderated for some reason I'm not aware of. I suggested you to use this expression instead, if both fields [Date Received Month/Year] and [Upload Month/Year] have the same format:

Sum({<[Date Received Month/Year]=P([Upload Month/Year]), [Sales Channel]={'FriendsLife'},[Conversion Status]={'2 Tender Doc Issued'}, Status={'Won','Live'}>}APE)

Hope that helps.

Miguel

[samvile18]

I'd like to thank everyone for taking the time and effort in replying to my question, I've tried a few of the answers and some work and some don't!

Thanks again for the help.