Top 1 for multiple measure in single widget sac

[thayumanavan]

I have 6 measure and 1 dimension[staff] in this i want to get top 1 of each measure in single widget. Please explain me how can we done in sac 

Regards,
Thayu.

[JBARLOW]

Hi there, i'm just trying to understand the specific ask

[JBARLOW]

So without a planning model, yep we're left with using text boxes to give the visual impression of a table.
In this example I only used 3 measures just to see if it works.
This should give you better performance and I'm sure my code/logic can be greatly improved to run more efficiently.

The 'table' on the right is 9 text boxes.   
T1,T2,T3    --  T2 is the product, T3 is the value
T4,T5,T6    --  T5 is the product, T6 is the value
T7,T8,T9    --  T8 is the product, T9 is the value

T1,T4,T7 -- I just hardcoded the text in as the values never change

Table_3.rankBy( {  relatedDimensions:{[Alias.MeasureDimension]:"M1"},rankOrder:RankOrder.Top,value:1});

var sel = Table_3.getSelections()[0];
var result = Table_3.getDataSource().getResultMember("Product",sel).description;
T2.applyText("Product"+"  "+result);
var arr = Table_3.getDataSource().getResultSet();
var value=   ConvertUtils.stringToNumber(  arr[0][Alias.MeasureDimension].rawValue);
var V2=ConvertUtils.numberToString(value);
	{T3.applyText(V2);}


//----------------------------------------------------------------------------

if(T3.getPlainText().length>0)
	{
		Table_3.rankBy( {  relatedDimensions:{[Alias.MeasureDimension]:"M2"},rankOrder:RankOrder.Top,value:1});
sel = Table_3.getSelections()[0];
result = Table_3.getDataSource().getResultMember("Product",sel).description;

T5.applyText("Product"+"  "+result);
arr = Table_3.getDataSource().getResultSet();
value=   ConvertUtils.stringToNumber(  arr[1][Alias.MeasureDimension].rawValue);
V2=ConvertUtils.numberToString(value);
	T6.applyText(V2);
	
	}


if(T6.getPlainText().length>0)
	
	{
		Table_3.rankBy( {  relatedDimensions:{[Alias.MeasureDimension]:"M3"},rankOrder:RankOrder.Top,value:1});
sel = Table_3.getSelections()[0];
result = Table_3.getDataSource().getResultMember("Product",sel).description;

T8.applyText("Product"+"  "+result);
arr = Table_3.getDataSource().getResultSet();
value=   ConvertUtils.stringToNumber(  arr[2][Alias.MeasureDimension].rawValue);
V2=ConvertUtils.numberToString(value);
	T9.applyText(V2);
	
	}
Table_3.removeRanking();

[thayumanavan]

Hi @JBARLOW Thank you so much for the solution. Yes its improved the performance but now its taking around 29 sec because i am using 14 text boxes upto 10 text boxes performance was fine. Thank you so much it's a nice thought. please let me know is there anyway to bring under 20 sec. 

Table_6.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"33558012-7484-4421-3006-224195070345"},rankOrder:RankOrder.Top,value:1});

var sel =  Table_6.getSelections()[0];

var result = Table_6.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

//T2.applyText(result);

var arr= Table_6.getDataSource().getResultSet();

var value= ConvertUtils.stringToNumber(arr[0][Alias.MeasureDimension].rawValue);

var v2=ConvertUtils.numberToString(value);

{T3.applyText(result+" "+v2);}

if(T3.getPlainText().length>0)

{

Table_6.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"31948283-6646-4859-3131-200104570027"},rankOrder:RankOrder.Top,value:1});

sel = Table_6.getSelections()[0];

result = Table_6.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

//T5.applyText(result);

arr = Table_6.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[1][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T6.applyText(result+" "+v2);}

}

if(T6.getPlainText().length>0)

{

Table_6.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"70672825-9386-4722-8931-171337372722"},rankOrder:RankOrder.Top,value:1});

sel = Table_6.getSelections()[0];

result = Table_6.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_6.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[2][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T2.applyText(result+" "+v2);}

}

Table_6.removeRanking();

Table_13.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"21113101-3185-4544-3738-248129545131"},rankOrder:RankOrder.Top,value:1});

sel = Table_13.getSelections()[0];

result = Table_13.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_13.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[0][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T7.applyText(result+" "+v2);}

Table_13.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"31154260-5820-4066-3722-876489481235"},rankOrder:RankOrder.Top,value:1});

sel = Table_13.getSelections()[0];

result = Table_13.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_13.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[1][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T8.applyText(result+" "+v2);}

Table_13.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"22808329-5639-4065-3947-023820268262"},rankOrder:RankOrder.Top,value:1});

sel = Table_13.getSelections()[0];

result = Table_13.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_13.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[2][Alias.MeasureDimension].formattedValue);

v2=ConvertUtils.numberToString(value);

{T9.applyText(result+" "+v2);}

Table_13.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"34898379-6717-4398-3299-236443852618"},rankOrder:RankOrder.Top,value:1});

sel = Table_13.getSelections()[0];

result = Table_13.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_13.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[3][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T10.applyText(result+" "+v2);}

Table_13.removeRanking();

Table_15.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"97751905-9347-4728-3713-314650871608"},rankOrder:RankOrder.Bottom,value:1});

sel = Table_15.getSelections()[0];

result = Table_15.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_15.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[0][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T11.applyText(result+" "+v2);}

Table_15.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"27450737-0012-4509-3581-127466913998"},rankOrder:RankOrder.Bottom,value:1});

sel = Table_15.getSelections()[0];

result = Table_15.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_15.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[1][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T12.applyText(result+" "+v2);}

Table_15.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"15705304-5111-4474-3555-936099895243"},rankOrder:RankOrder.Bottom,value:1});

sel = Table_15.getSelections()[0];

result = Table_15.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_15.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[2][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T13.applyText(result+" "+v2);}

Table_14.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"15208108-3015-4751-3637-330770027191"},rankOrder:RankOrder.Bottom,value:1});

sel = Table_14.getSelections()[0];

result = Table_14.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_14.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[0][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T14.applyText(result+" "+v2);}

Table_14.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"17352037-1034-4910-8155-931775798257"},rankOrder:RankOrder.Bottom,value:1});

sel = Table_14.getSelections()[0];

result = Table_14.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_14.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[1][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T15.applyText(result+" "+v2);}

Table_14.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"16781443-8313-4628-3400-127855323264"},rankOrder:RankOrder.Bottom,value:1});

sel = Table_14.getSelections()[0];

result = Table_14.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_14.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[2][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T16.applyText(result+" "+v2);}

Table_14.rankBy({relatedDimensions:{[Alias.MeasureDimension]:"17451666-6615-4917-3990-715479087883"},rankOrder:RankOrder.Bottom,value:1});

sel = Table_14.getSelections()[0];

result = Table_14.getDataSource().getResultMember("2CHOW5C4IK4P23Y43CLQ78N2J8Y",sel).description;

arr = Table_14.getDataSource().getResultSet();

value=   ConvertUtils.stringToNumber(  arr[3][Alias.MeasureDimension].rawValue);

v2=ConvertUtils.numberToString(value);

{T17.applyText(result+" "+v2);}
Regards,
Thayu.

[JBARLOW]

Can you provide more detail - or an example of the data and desired result?

e.g.  We data in the table on the left and we want to see the table on the right?
        i.e. the Top sales volume for each store regardless of product etc.

[thayumanavan]

Hi @JBARLOW, Thanks for reply Please find the sample

Like this I want to do in sac live connection
In the left side table we have sales volume and sales quantity from that I want to get top 1 product of sales volume and top 1 product of sales quantity.

Regards,
Thayu.

[thayumanavan]

Hi @JBARLOW  Finally find a solution in with some scripting but its taking around 40 sec to l

In Table 1 :
Created a aggregation Calculation with the operation max for the measure which I want to get top 1.
In Table 2 :
I added the aggregation calculation on column and dimension staff on rows 
Sac Scripting to find top 1 dimension for different measure in OnInitialization is working but its taking around 30 sec to load

var a = Table_7.getDataSource().getResultSet();

var b = Table_14.getDataSource().getResultSet();

var m1 = "";

var m2 = "";

var m3="";

var m4="";

var m5="";

var m6="";

var m7="";

var d1="";

var d2="";

var d3="";

var d4="";

var d5="";

var d6="";

var d7="";

if (a.length >= 2) {

    var pickunit1 = a[0][Alias.MeasureDimension].formattedValue;

    var pickunit2 = a[1][Alias.MeasureDimension].formattedValue;

var pickunit3 = a[2][Alias.MeasureDimension].formattedValue;

var pickunit4 = a[3][Alias.MeasureDimension].formattedValue;

var pickunit5 = a[4][Alias.MeasureDimension].formattedValue;

var pickunit6 = a[5][Alias.MeasureDimension].formattedValue;

var pickunit7 = a[6][Alias.MeasureDimension].formattedValue;

var pickunit8 = a[7][Alias.MeasureDimension].formattedValue;

var pickunit9 = a[8][Alias.MeasureDimension].formattedValue;

var pickunit10 = a[9][Alias.MeasureDimension].formattedValue;

var pickunit11=a[10][Alias.MeasureDimension].formattedValue;

var pickunit12=a[11][Alias.MeasureDimension].formattedValue;

var pickunit13=a[12][Alias.MeasureDimension].formattedValue;

var pickunit14=a[13][Alias.MeasureDimension].formattedValue;

var pickunit15=a[14][Alias.MeasureDimension].formattedValue;

var pickunit16=a[15][Alias.MeasureDimension].formattedValue;

var pickunit17=a[16][Alias.MeasureDimension].formattedValue;

var pickunit18=a[17][Alias.MeasureDimension].formattedValue;

var pickunit19=a[18][Alias.MeasureDimension].formattedValue;

var pickunit20=a[19][Alias.MeasureDimension].formattedValue;

var pickunit21=a[20][Alias.MeasureDimension].formattedValue;

    for (var i = 0; i < b.length; i++) {

        var dimValue = b[i]["2CHOW5C4IK4P23Y43CLQ78N2J8Y"].description;

        var pickunit = b[i][Alias.MeasureDimension].formattedValue;

        if (pickunit === pickunit1) {

            m1 = dimValue;

        }

        if (pickunit === pickunit2) {

            m2 = dimValue;

        }

if(pickunit===pickunit3)

{

m3 = dimValue;

}

if(pickunit===pickunit4)

{

m4 = dimValue;

}

if(pickunit === pickunit5)

   {

    m5= dimValue;

   }

if(pickunit ===pickunit6)

{

m6 = dimValue;

}

if(pickunit===pickunit7)

{

m7 = dimValue;

}

if(pickunit===pickunit8)

{

d1 = dimValue;

}

if(pickunit===pickunit9)

{

d2 = dimValue;

}

if(pickunit===pickunit10)

{

d3 = dimValue;

}

if(pickunit===pickunit11)

{

d4 = dimValue;

}

if(pickunit===pickunit12)

{

d5 = dimValue;

}

if(pickunit===pickunit13)

{

d6 = dimValue;

}

if(pickunit===pickunit14)

{

d7 = dimValue;

}

    }

}

    var TEXT_VALUE22 = Text_37.getPlainText();

    Text_37.applyText(TEXT_VALUE22+""+m1+" "+pickunit1+" "+m2+" "+pickunit2+" "+m3+"  "+pickunit3+" "+m4+" "+pickunit4+" "+m5+" "+pickunit5+" "+m6+" "+pickunit6+" "+m7+" "+pickunit7);

var TEXT_Value23 = Text_38.getPlainText();

Text_38.applyText(TEXT_Value23+" "+d1+" "+pickunit8+" "+d2+" "+pickunit9+" "+d3+" "+pickunit10+" "+" "+d4+" "+pickunit11+" "+d5+""+pickunit12+" "+d6+" "+pickunit13+" "+d7+" "+pickunit14);

    var TEXT_Value24 = Text_39.getPlainText();

    Text_39.applyText(TEXT_Value24+" "+pickunit14+" "+pickunit15+" "+pickunit16+" "+pickunit17+" "+pickunit18+" "+pickunit19+" "+pickunit20+" "+pickunit21);

    console.log(d1);*/

Output:

Please let me know in there any way to Improve the performance

Regards,
Thayu.

[JBARLOW]

Impressive scripting thought process! I need to get my head round it, but initial thoughts are we should be able to improve performance