Class

[eptaqlik]

Hi,

I would like in a pivot table to insert a class that tells me for each material for how many months it has been sold and group the materials for that class.

I attach excel file with starting data and data as I wish

Thanks for your help.

Marco

[jyothish8807]

Hi Marco,

Please refer the sample attached.

Br,

KC

[YoussefBelloum]

here is another alternative if you can't do it on the script:

Calculated dimension:

=aggr(Count({<CodArticolo-={"=sum(Sales1)=0"}>}Sales1)

+Count({<CodArticolo-={"=sum(Sales2)=0"}>}Sales2)

+Count({<CodArticolo-={"=sum(Sales3)=0"}>}Sales3)

+Count({<CodArticolo-={"=sum(Sales4)=0"}>}Sales4)

+Count({<CodArticolo-={"=sum(Sales5)=0"}>}Sales5)

+Count({<CodArticolo-={"=sum(Sales6)=0"}>}Sales6),CodArticolo)

or this expression:

=Count({<CodArticolo-={"=sum(Sales1)=0"}>}Sales1)

+Count({<CodArticolo-={"=sum(Sales2)=0"}>}Sales2)

+Count({<CodArticolo-={"=sum(Sales3)=0"}>}Sales3)

+Count({<CodArticolo-={"=sum(Sales4)=0"}>}Sales4)

+Count({<CodArticolo-={"=sum(Sales5)=0"}>}Sales5)

+Count({<CodArticolo-={"=sum(Sales6)=0"}>}Sales6)

[neha_shirsath]

Hi,

Can you elaborate more, as in excel provided on which basis class will be calculated?

Provided data is not sufficient to get output.

-Regards,

Neha

[eptaqlik]

Hi KC,

I have my data in the "Start" sheet

in the "Result" sheet I calculated the class by counting the period in which there were sales and I ordered the data in descending order of class.

many tks

Marco

[eptaqlik]

Hi Neha,

I have my data in the "Start" sheet

in the "Result" sheet I calculated the class by counting the period in which there were sales and I ordered the data in descending order of class. Only the column "A" was calculated the others field in column are the same.

many tks

Marco

[jyothish8807]

Hi Marco,

The same has been implemented in attached sample above, please have a look

Script:

A:

LOAD * INLINE [

    CodArticolo, Sales 1, Sales 2, Sales 3, Sales 4, Sales 5, Sales 6

    AA, 0, "5,754.00", 0, "4,790.00", 0, 0

    BB, 0, 0, 0, 0, 250.00, 0

    CC, 0, 712.00, 0, 0, 0, 0

    DD, "5,825.40", "9,475.22", "1,308.10", "1,924.50", "1,240.60", "1,824.60"

    EE, "11,715.60", "2,170.20", "4,188.69", 841.64, "9,310.50", "4,360.43"

    GG, 980.00, "1,225.00", 244.00, 0, 980.00, 0

    HH, "1,801.67", 485.68, 263.00, 255.46, "1,577.48", 0

    II, 0, 0, 230.00, 0, 0, 0

    LL, "9,503.78", 0, "4,930.45", "1,359.37", 0, "2,719.41"

    MM, 526.48, 0, 0, 583.42, "1,886.68", 0

];

B:

CrossTable(Sale_New, Data)

LOAD *

resident A;

NoConcatenate

c:

LOAD

CodArticolo,

if(Data>0,1,0) as Flag

Resident B;

DROP table B;

[eptaqlik]

Hi KC,

I change some things in your script but without your help I cannot solve the problem.

many tks.

Marco

A:

LOAD * INLINE [

    CodArticolo, Sales 1, Sales 2, Sales 3, Sales 4, Sales 5, Sales 6

    AA, 0, "5,754.00", 0, "4,790.00", 0, 0

    BB, 0, 0, 0, 0, 250.00, 0

    CC, 0, 712.00, 0, 0, 0, 0

    DD, "5,825.40", "9,475.22", "1,308.10", "1,924.50", "1,240.60", "1,824.60"

    EE, "11,715.60", "2,170.20", "4,188.69", 841.64, "9,310.50", "4,360.43"

    GG, 980.00, "1,225.00", 244.00, 0, 980.00, 0

    HH, "1,801.67", 485.68, 263.00, 255.46, "1,577.48", 0

    II, 0, 0, 230.00, 0, 0, 0

    LL, "9,503.78", 0, "4,930.45", "1,359.37", 0, "2,719.41"

    MM, 526.48, 0, 0, 583.42, "1,886.68", 0

];

B:

CrossTable(Sale_New, Data)

LOAD *

resident A;

NoConcatenate

C:

LOAD

*,

if(Data>0,1,0) as Flag

Resident B;

DROP table A;

DROP table B;

[jyothish8807]

Hi Marco,

In Table "C" dont use load *. It will create synthetic key. Keep it like this:

CodArticolo filed will act as the key among both tables. You can join table A and Table C also if you want.

Also dont drop table A.

NoConcatenate

c:

LOAD

CodArticolo,

if(Data>0,1,0) as Flag

Resident B;

DROP table B;

[eptaqlik]

Hi KC,

many tks.

ciao

Marco