topic Re: SUM Firstsortedvalue in script in Brasil

SUM Firstsortedvalue in script

jsobrinho — Mon, 23 Jul 2018 19:39:10 GMT

Hi guys,

Is it possible to create a sum Firstsortadedvalue in the script?

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue2:

LOAD Customer,

sum(FirstSortedValue(distinct Product, -UnitSales,2)) as Value

Resident Temp

Group By Customer,Product

;

Re: SUM Firstsortedvalue in script

sunny_talwar — Mon, 23 Jul 2018 19:46:03 GMT

May be this

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue2:

LOAD Customer,

Sum(Value) as SumValue

Group By Customer;

LOAD Customer,

Product,

FirstSortedValue(distinct Product, -UnitSales,2) as Value

Resident Temp

Group By Customer, Product;

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue2:

LOAD Product,

Sum(Value) as SumValue

Group By Product;

LOAD Customer,

Product,

FirstSortedValue(distinct Product, -UnitSales,2) as Value

Resident Temp

Group By Customer, Product;

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue2:

LOAD Sum(Value) as SumValue;

LOAD Customer,

Product,

FirstSortedValue(distinct Product, -UnitSales,2) as Value

Resident Temp

Group By Customer, Product;

Re: SUM Firstsortedvalue in script

Anonymous — Mon, 30 Jul 2018 13:39:36 GMT

Hello Here is some Script that i made for my works it could help you tu understand how to deal with your data

Assume that i have a variable vRecord_CA_Month (Best_month_Sales)

vRecord_CA_Month =Max(aggr(Max(CA_Month),Month_CA,Year_CA))

CA_Month | Month_CA | Year_CA

1235.1 07 2018

....

Here i fetch for the row whith contains le Max Value.

I have to substract some "CHARGE" from vRecord_CA_Month
CHARGE are stored in another table but corresponding tu Month_CA and Year_CA ... this is my link !!

To make this link i had to create a date that corressponds to on other table

=Makedate(

(Num#(FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) ),'0')),

(Num#(FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) ),'0'))

)

Expression Result look like ( Dont panic !!!)

Expression = vRecord_CA_Month

Sum(

{

[Date.autoCalendar.Month] ={'$(=Month(Makedate((Num#(FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) ),'0')), (Num#(FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) ),'0')) )))'},

[Date.autoCalendar.Year] = {'$(=Year(Makedate((Num#(FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) ),'0')), (Num#(FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) ),'0')) )))'},

[ODS_GLOBAL.Type de pièce] ={'Avoir'}

} [Total HT])