Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have created a base table using the straight table, Where I need to develop charts from this base table.
This my table look like, I have mentioned below how I have calculated.
ItemCode - ItemCode (Sorted By Rank Expression)
Rank - =Rank(Sum(Aggr(if(Sum({$<TTYPE={'Stock'},DOCDATE={"<=$(=Date(max(DOCDATE)-1,'DD/MM/YYYY'))"}>}STOCK_QTY)>0,
Sum(Aggr(Sum({$<TTYPE={'Stock'},DOCDATE={"<=$(=Date(max(DOCDATE)-1,'DD/MM/YYYY'))"}>}STOCK_VALUE),LOCATION,SUBLOCATION,ITEMTYPE,ITEMGROUP,ITEMSUBGROUP,ITEMCODE,PITEMCODE,POITEM,
UOMCODE,ITEMCATERGORY,ITMORDCAT,ITEMID)),0),LOCATION,SUBLOCATION,ITEMTYPE,ITEMGROUP,ITEMSUBGROUP,ITEMCODE,PITEMCODE,POITEM,
UOMCODE,ITEMCATERGORY,ITMORDCAT,ITEMID))
,2,1)
Then I need to capture the Cumulative Stock Value, for an example, in the first line it should show 36,796,304, and in the second line it should show 36,796,304+14,989,428. More specifically, it should get the cumulative from rank. Same dataset if I export to excel, Take Itemcode as A column, Rank as B Column, Stock Value as C Column and in D column i have to write the expression =SUMIF($B$2:$B$13190,"<="&B2,$C$2:$C$13190). This expression, i need to achieve in Qlik Sense.
As of now in the above table, I have used Above() as follows;
Cumulative Stock Value - RangeSum(Above(Sum(Aggr(if(Sum({$<TTYPE={Stock},DOCDATE={"<=$(=Date(max(DOCDATE)-1,'DD/MM/YYYY'))"}>}STOCK_QTY)>0,
Sum(Aggr(Sum({$<TTYPE={Stock},DOCDATE={"<=$(=Date(max(DOCDATE)-1,'DD/MM/YYYY'))"}>}STOCK_VALUE),LOCATION,SUBLOCATION,ITEMTYPE,ITEMGROUP,ITEMSUBGROUP,ITEMCODE,PITEMCODE,POITEM,
UOMCODE,ITEMCATERGORY,ITMORDCAT,ITEMID)),0),LOCATION,SUBLOCATION,ITEMTYPE,ITEMGROUP,ITEMSUBGROUP,ITEMCODE,PITEMCODE,POITEM,
UOMCODE,ITEMCATERGORY,ITMORDCAT,ITEMID))
,0,RowNo()
))
Using this Cumulative Stock Value column, i have created Cumulative Stock Value % as **bleep**. Stock Value/total Stock Value. And using % I have created the ABC category column.
The issue I have now is, as I have rangeabv() to Cumulative stock value, I cannot take this ABC Category Column into a bar chart as a dimension. Is there any other work around to achieve this or somehow achieve that excel formula inside Qlik table.
Note: - I have calculated stock qty, value in the front end, as user needs to check stock by backdating.