set analysis using 2 variables

[#1]

HI All,

Set analysis can be frustrating sometimes.

I've been trying to tweak a formula to improve storage on a table. Currently I'm extracting the year from a date on the import but i wanted to make this neater by using a variable instead

v_year  = a year selectable by the user

v_Quals = date a user qualified , which is set to BKKReg

so originally i was using this for set analysis

=count({$<BKKRegYear={$(v_year)}>} distinct person_id)    - this works fine

now BKKRegYear is just  =  year(BKKReg) which is done on the load script but i wanted to remove this column altogether and just use the column and manipulate it in the set analysis.

I use

=month($(v_Quals))  as a dimension which is working fine

but using

=count({$<year($(v_Quals))={$(v_year)}>} distinct person_id)

throws up an error in expression and gives me no values

Can anyone shed some light on this, this set analysis expression is eluding me

many thanks

[ciaran_mcgowan]

Hi Lee,

This won't answer your whole question but when using a variable in set analysis (after the 😃 you need to write it like this: ={'=$(v_year)'}

In terms of using a variable instead of a field name, I'll need to look into a bit more. Any chance you could post a copy of your application?

[#3]

HI Ciaran

many thanks but I used a variable originally for the v_year

=count({$<BKKRegYear={$(v_year)}>} distinct person_id)   which worked fine

but adding your extra brackets gave an incorrect count (see below)

=count({$<BKKRegYear={'=$(v_year)'}>} distinct person_id)

The part im trying to fathom out is the replacement of the BKKRegYear bit with year($(v_Quals)) or some such syntax

due to the nature of the data i can't submit the whole app

[Anil_Babu_Samineni]

Lee,

How you want to show Unique vales. If you want to show unique values for BKKRegYear also. You need to change that distinct to front side

=count({$<year((v_Quals))={=$(v_year)}>} distinct person_id)

As per your requirement you should deliver that. So, here what you want to do?

[rubenmarin]

Hi Lee, wich field has to keep the selections? There is a field for the year or there is only BKKReg? Is BKKReg a Date?

Supposing you only have BKKReg, wich is date in format DD/MM/YYYY you can use:

=count({$<BKKReg={"*/$(v_year)}">} distinct person_id)

It will select all dates ended in the year selected.

[#6]

HI Ruben

As i mentioned i don't just have BKKReg I have several quals hence the use of a variable

Both fields would keep the selections

=count({$<year($(v_Quals))={$(v_year)}>} distinct person_id)

v_Qual = is actually a date behind a certain qualification

v_Year  = is a selectable year

so the table structure is similar to that below and I'll have several buttons which drive the year by changing the variable on clicking  and another set to drive the column header i.e BKKReg, AKKReg, or DKKReg

person_id	BKKReg	AKKReg	DKKReg
123456	01/01/2016	01/01/2015
234567			01/02/2016
345678	02/03/2016
456789		03/02/2016

[rubenmarin]

Hi Lee, I don't understand how you use the v_Qual variable and the relation with the different date fields.

Set analisys is a filter, a filter is applied through selections, and selections are done in fields, so the first part of the set analysis sentence should have an existing field name where it will apply the selections. And doesn't seems that "year($(v_Quals))" will return an existing field name.

You can have a variable -or a field with 'Only One Value Selected'- so the user can select wich date field to apply the filter, this field or variable can have 3 options: BKKReg, AKKReg or DKKReg.

In this case you can do:

=count({$<[$(new_variable_name)]={"*/$(v_year)}">} distinct person_id)

This "[$(new_variable_name)]" will be checked as an error but it works.

[#8]

HI Ruben

That's very helpful

Basically you have 3 fields e.g. A, B, C,  which only contain dates ( the date the candidate has registered). So I have 3 text boxes which set the variable to the column the user wants focus on, i.e. name of column A, B, C and all I'm trying to do is use the year of that column , not the full date.

I then want to filter the said column by the 2nd variable , which is selected via 3 other year text boxes.  2014,2015,2016

hence 2 variables

so using your example all im trying to do is get entries in the v_Quals column which year match the year selected

so thought this may work as its the year of the v_quals column thats required and as the column is constantly moving its not like i can use a list box - does that make sense

=count({$<[year($(v_Quals))]={"*/$(v_year)}">} distinct person_id)

[jonathandienst]

You cannot use an expression on the LHS of a set expression. This can only be a field. I suggest that you create a year field (or fields) in the load and then use that field (or those fields) in the expression.

[rubenmarin]

Hi Lee, as said before, you need to use an existent field name, doesn't matters the name of the column or whatever, only existing field names, in your example: 'A', 'B' or 'C'.

[year($(v_Quals))] is not an existing field name.

I attach a simple example to check.