topic Re: group by expression using set analysis in QlikView

group by expression using set analysis

Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: group by expression using set analysis

Anil_Babu_Samineni — Sat, 15 Oct 2016 18:01:04 GMT

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

Re: group by expression using set analysis

sunny_talwar — Sat, 15 Oct 2016 22:30:22 GMT

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)).

Re: group by expression using set analysis

Sun, 16 Oct 2016 03:13:59 GMT

Hi Anil Babu,

Please find the attached document, thanks

Re: group by expression using set analysis

Sun, 16 Oct 2016 03:36:28 GMT

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