group by expression using set analysis

[#1]

Hi Expert,

Please find the attached qlikview document, any idea how to build this report using below source. Thanks

Sales:

LOAD * INLINE [

    Country, Week NO, Category, Launch, Stock type, Article, SOH, Sold Qty Week, TotalNetPrice, TotalLandedCost, Type_ID, Order Qty

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30270006, 400, 26, 80473, 31668, NB, 31

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-20780203, 316, 30, 77442, 24932, NB, 19

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-80680430, 601, 33, 77371, 25368, RO, 154

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30741445, 504, 25, 77146, 21166, RO, 10

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50780230, 504, 30, 74765, 31554, RO, 35

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50670452, 446, 99, 282234, 125982, NB, 8

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50780219, 244, 102, 276768, 116025, NB, 31

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30780217, 487, 80, 244943, 91000, RO, 3

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-80840056, 243, 100, 237215, 96610, RO, 2

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30270010, 546, 75, 231134, 81417, RO, 6

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-80150494, 242, 5, 10833, 4129, RO, 1

    Thailand, 38, Ladies Bag, 2015-L2, Seasonal, CK2-50780074, 288, 3, 9787, 3573, RO, 56

    Thailand, 38, Ladies Bag, 2016-L4 (JIT), Seasonal, CK2-80150527, 211, 6, 9703, 3786, RO, 20

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-20741422, 86, 4, 9319, 4114, RO, 2

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-80741408, 211, 4, 9319, 3574, NB, 7

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-50670436, 159, 3, 9188, 4534, NB, 1

    Thailand, 38, Ladies Bag, 2016-L2, Seasonal, CK2-50680373, 200, 4, 8935, 3303, RO, 2

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-50670432, 74, 4, 8506, 4155, UB, 1

];

Expected output,

[Anil_Babu_Samineni]

Does Expected output is in Excel? If yes, please share Excel

[sunny_talwar]

The way this is structured, you won't be able to achieve your desired result because of the limitations of QlikView. You will need to use Aggregate function within you calculated dimension and Aggr function can sort ascending, descending, load order, but cannot sort by expression. So there is not way to sort your calculated dimension with in ascending order of =Rank(TOTAL Sum(TotalNetPrice)).

[#4]

Hi Anil Babu,

Please find the attached document, thanks

[#5]

Hi Sunny,

Thanks for your prompt reply, Sorting not required for the summary report, can you explain how to add below expression in dimension place .

Grade

=if(

  rangesum(above(TOTAL ([Sales Value%]),0,RowNo(Total)))<0.6,'A Stocks',if(

  rangesum(above(TOTAL ([Sales Value%]),0,RowNo(Total)))>0.6 and rangesum(above(TOTAL ([Sales Value%]),0,RowNo(Total)))<0.9,'B Stocks',

  'C Stocks'

  )

  )

note: Sales Value%

=SUM(TotalNetPrice/SUM(TOTAL TotalNetPrice))

!