Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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
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?
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.
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 |
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.
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)
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.
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.