Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eptaqlik
Contributor III
Contributor III

Class

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

9 Replies
jyothish8807
Master II
Master II

Hi Marco,

Please refer the sample attached.

Br,

KC

Best Regards,
KC
YoussefBelloum
Champion
Champion

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
Specialist
Specialist

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Master II
Master II

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;

Comm_305768.JPG

Best Regards,
KC
eptaqlik
Contributor III
Contributor III
Author

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
Master II
Master II

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;

Best Regards,
KC
eptaqlik
Contributor III
Contributor III
Author

Hi KC,

many tks.

ciao

Marco