Introduce String value in Set Analysis expression !!

[#1]

Hello,

I have to variabilize amounts, the values ​​to be introduced in the expression of the set Analysis are actually not related in my data model ( and it shoud be not)  so to have good results, I have to add the different amounts value of each field forcing the sum of 4 fields because it does not match in the data model !! is to achieve an exceptional Analysis Chart @ data (below the Variable Set Analysis field shows the expression).

The source of data is an Excel file, which can be changed from time to time, this file is linked with the modele only the by brand field ( 'MARQUE '.

I managed to develop a procedure (QVS ) that generates set analysis expressions , and creates the fields 'value (Set Analysis)' corresponding to the CLASS, VARIABLE and BRAND fields.

By using the hard, that will be like : if (VARIABLE = 'VAR 1', sum ({<$ (******)>} Sales), if (VARIABLE = 'VAR 14', sum ({ <$ (******)>} Sales), etc ...

*****: Any expression that exists in the Value field (Set Analysis) corresponding to VARIABLE (even online).

I am looking for a solution that allows to introduce String of the Value column (Set Analysis) in an amount for each line of the table as: sum ({<$ (= '$ (Value) (Set Analysis))>} Sales) !!

Subcontractor for any other proposals and explanations.

Thank you in advance.

[johnw]

I'm having a little trouble following, but it seems you want a different set analysis expression for every line in the table, and these set analysis expressions are already built in script as variables? Does which variable you want to use depend on the value in field CATEGORIE? Maybe you could build one more variable in your script to pick the right expression based on CATEGORIE.

pick(match(CATEGORIE,'CAT_1','CAT_13',...)
,sum({<$(V_1)>} Sales)
,sum({<$(V_13)>} Sales)
...)

I do that in one of my applications, or something very much like that. I hated needing to do it, but it was the only way I could figure out to make a particular chart work EXACTLY like a user wanted, and the user was very insistent that it work EXACTLY like he wanted, not merely present the same information.

[johnw · Accepted solution]

For more on this sort of approach:

Evaluating "sets" in the context of a dimension

[#4]

Hello John ,

Thank you for your response , you solution works . But , if I need ( User needs) to change the name of ' CAT1' or 'CAT2'

and to change the composant of the set analysis 'V_1 ' or V_2'  from the data source ( Excel File ) , how can he found the good value !!??

[johnw]

Are you saying that the user controls the Excel file? So they might, say, add and remove CATEGORIEs, or change what a given CATEGORIE includes?

I'm imagining your spreadsheet looking something like:

CATEGORIE  CATEGORY   FAMILY                         GROUP TYPE
CAT_1      VAR1,VAR2  VAR1                           VAR1  VAR1
CAT_13     VAR1,VAR2  VAR1,VAR2,VAR3,VAR4,VAR5,VAR6  ...

I'm sure it's not exactly that, but containing that kind of information. If so, that's what I was already visualizing when I made my suggestion, just figured you'd maintain it instead of a user, but certainly your script doesn't know who maintains it. Either way, you read in the spreadsheet and build all your variables from it.

Ah, or maybe you didn't catch part of what I said, that the pick(match(...)...) itself is just another variable built in your script from the spreadsheet? It's not something you're typing in and maintaining manually. You'd build it automatically from the spreadsheet just like all your other variables.

Or have I misunderstood what you're asking?

[#6]

Hello,

It's just what you have imaginated by the spreadsheet , and it's look like what you have write.

User controls the Excel file , yes, and they can add/remove/change CATEGORY and what a given VATEGORY includes.

I tryng an other function, in my script , it works by IF , but not the Pick( match (...)...) !! and of corse the function takes a lot of time to visualise the CROSS table on designer .

[johnw]

I suspect that pick(match()) is more efficient than a lot of nested IFs, but I don't know for sure that it is. I can't help with the resulting expression being hard to see or to understand. But in case it's of use, here's the code where I build a pick(match()) like this. In my case, each validation month got its own expression.

[Months]:
LOAD date(fieldvalue('Validation Month',recno())) as [Month]
AUTOGENERATE fieldvaluecount('Validation Month')
;
LET vExpression = 'pick(1+match([Validation Month]';

FOR I = 1 TO noofrows('Months')
    LET vExpression = vExpression & ',' & chr(39) & date(fieldvalue('Month',$(I)),'MMM YYYY') & chr(39);
NEXT

LET vExpression = vExpression & '),0';

FOR I = 1 TO noofrows('Months')
    LET vExpression = vExpression & ',vm' & $(I);
    LET vm$(I) = ...you're already building these just fine...;
NEXT

LET vExpression = vExpression & ')';

DROP TABLE [Months];