Range (max-min) as a calculated dimension

[paweln]

Hello,

I'm trying to create dimension like "Range" below:

[Taoufiq_Zarra · Accepted solution]

Hello,

one solution :

Data:

CrossTable(value, Data)
load * inline [
ID,value1,value2,value3,value4
xx,21,17,50,2,48
yy,31,0,12,40
zz,12,14,18,9
];

left join

load ID,Max(Data)-Min(Data) as Range resident Data group by ID;

output:
load distinct ID ,Range resident Data;

Tmp:

Generic LOAD ID,value,Data resident Data;

drop table Data;


FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));
  //trace $(i) - '$(vTable)';
  IF WildMatch('$(vTable)', 'Tmp.*') THEN 

  LEFT JOIN ([output]) LOAD distinct * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF 

NEXT i

output :

[Taoufiq_Zarra · Accepted solution]

one solution if number of Field is fixed :

load RangeMax(value1,value2,value3,value4)-RangeMin(value1,value2,value3,value4) as Range,* inline [
ID,value1,value2,value3,value4
xx,21,17,50,2,48
yy,31,0,12,40
zz,12,14,18,9
];

output :

[paweln]

Excellent! Thank you. I like second solution and don't understand the first one. Is there any advantage of the first one?

[Taoufiq_Zarra]

the first one is good if the Fields are dynamic on like this

value1,value2,value3,value4

and another

value1,value2,value3,value4,value5,value6,...

[paweln]

Thank you again, it's very helpful. God bless you!

Now I have another issue with the same data. Let's say I have fixed no of fields (value1-value4). Now I need to calculate Standard Deviation StDev() on all values from fields value1,value2, value3 and value 4 but that function takes only one parameter... How can I collect all single values from 4 dimensions and create one set of values? What's more, values equal to 0 should be omitted.

[Taoufiq_Zarra]

I think in this case you can use the first version.

Data:

CrossTable(value, Data)
load * inline [
ID,value1,value2,value3,value4
xx,21,17,50,2,48
yy,31,0,12,40
zz,12,14,18,9
];

left join

load ID,StDev(Data) as STDEV resident Data where Data<>0 group by ID ;

output:
load distinct ID ,STDEV resident Data;

Tmp:

Generic LOAD ID,value,Data resident Data;

drop table Data;


FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));
  //trace $(i) - '$(vTable)';
  IF WildMatch('$(vTable)', 'Tmp.*') THEN 

  LEFT JOIN ([output]) LOAD distinct * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF 

NEXT i

output :

[paweln]

Thank you for your prompt answer but that is not exactly what I described. I need only one StDev result. Not StDev for individual IDs.  In this case it will be around 14,86

[Taoufiq_Zarra]

@paweln 

In this case I think you need just to add CrossTable , like :

Data:

CrossTable(value, Data)
load * inline [
ID,value1,value2,value3,value4
xx,21,17,50,2,48
yy,31,0,12,40
zz,12,14,18,9
];

and in the interface KPI :

=StDev(Data)

output :

[paweln]

Dear Taoufiq,

thank you but.. How to exclude 0 here? I don't want to treat 0 as a value.

I would like it to be also subjected to selections for example I may need to choose only xx and yy IDs.