topic Re: set analysis with variable in QlikView

set analysis with variable

Wed, 28 Nov 2012 07:57:18 GMT

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

Anonymous — Wed, 28 Nov 2012 08:14:49 GMT

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

Wed, 28 Nov 2012 08:33:36 GMT

no it's not working,but thank you

Re: set analysis with variable

Anonymous — Wed, 28 Nov 2012 08:43:14 GMT

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

Wed, 28 Nov 2012 09:11:48 GMT

no not working

Re: set analysis with variable

Thu, 29 Nov 2012 01:45:20 GMT

Try:

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

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

Re: set analysis with variable

Thu, 29 Nov 2012 02:02:24 GMT

Wow ,Thank you ,awsome!

Re: set analysis with variable

Thu, 29 Nov 2012 02:56:35 GMT

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

Thu, 29 Nov 2012 09:58:00 GMT

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

Thu, 29 Nov 2012 18:26:40 GMT

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)

QlikView's Help has more information about set analysis.

Re: set analysis with variable

Fri, 30 Nov 2012 02:14:21 GMT

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)