10 Replies Latest reply: Nov 29, 2012 9:14 PM by llxq_yoyo

# set analysis with variable

I want to use varaible in analysis

for example

SYear and SMonth are variables.

The below expression work well

count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'},

UsageYear = {'\$(=SYear)'},UsageMonth ={'\$(=SMonth)'}>} distinct OrderId)

But if I want to  use function on the varaible ,how should I write the set analysis?

I mean if now I have a varaible named ReportDate,date type

I want to use Year function to get the year part. like Year(\$(=ReportDate))

then the analysis should be

count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'},

UsageYear = {'Year(\$(=ReportDate))'},UsageMonth ={'Month(\$(=ReportDate))'}>} distinct OrderId)

I dont know how could i get it work?

• ###### Re: set analysis with variable

hi llxq_yoyo

try this \$(ReportDate)

like,

count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'}, UsageYear = {'Year(\$(=\$(ReportDate)))'}, UsageMonth = {'Month(\$(=\$(ReportDate)))'}>} distinct OrderId)

Regards,

Akbar

• ###### Re: set analysis with variable

no it's not working,but thank you

• ###### Re: set analysis with variable

try to insert dimension year or month before variable \$(ReportDate)

set this before :

LET ReportDate = Periode;

let's try :

count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'}, UsageYear = {'Year(\$(=Year(\$(ReportDate))))'}, UsageMonth = {'Month(\$(=Month(\$(ReportDate))))'}>} distinct OrderId)

Regards,

Akbar

• ###### Re: set analysis with variable

no not working

• ###### Re: set analysis with variable

Try:

count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'},

UsageYear = {'=Year(\$(=ReportDate))'},UsageMonth ={'=Month(\$(=ReportDate))'}>} distinct OrderId)

• ###### Re: set analysis with variable

Wow ,Thank you ,awsome!

• ###### Re: set analysis with variable

And another question,can you tell me the rules of set analysis. I don't understand why we should write like this. or do you have any references about writing set analysis which can guide me a correct direction?

• ###### Re: set analysis with variable

Hey,i thought it worked,but actually,it just match the grammer,so i could run it.

but actually it didn't make any effects.

I mean if it worked .it just calculate this month's data,but it seems calculate all the months' data

• ###### Re: set analysis with variable

Try:

count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'},

UsageYear = {"=Year('\$(=ReportDate)')"},UsageMonth ={"=Month('\$(=ReportDate)')"}>} distinct OrderId)

Note the quotes in set modifier selection.

As for set analysis, you're actually dealing with three different concepts, set analysis, advanced search, and dollar sign expansion.

Let's simplify your expression for illustration:

count({<UsageYear = {"=Year('\$(=ReportDate)')"}>} OrderId)

This returns the number of orders in the full year specified by the ReportDate variable.

Now let's take the parts:

\$(=ReportDate)

This is the dollar sign expansion. It takes the contents of the variables and places in the expression. I suspect that \$(ReportDate) will also work in your case; \$(ReportDate) takes the contents of the variable while \$(=ReportDate) evaluates ReportDate and places the result of the evaluation into the expresion.

If ReportDate is set to 2012-11-29 then this produces the next part:

"=Year('2012-11-29')"

This is the advanced search (strings that start with =). You can use wildcards (*) or functions (e.g., sum, avg, year).

The year fuction will evaluate to 2012 to produce:

count({<UsageYear = {2012}>} OrderId)

• ###### Re: set analysis with variable

Than you for so detailed description,but it seems not right.

And I've figured out the right way.

It should be below:

count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'},

UsageYear = {\$(=Year('\$(ThisMonthEndDateVar)'))},

UsageMonth = {\$(=Month('\$(ThisMonthEndDateVar)'))}>} distinct OrderId)