Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

How to modify my table to display inventory turn over ratio vs On hand stock amount ?

Hi All


I have a table 1 , which is working fine,


This table able to show me the :-


inventory turn over ratio = base on average month sold amount / with on hand stock amount.


Now i like to view the inventory Turn over ratio vs on hand stock Amount. Look some thing like below :-


Inventory Turn over ratio........ On hand amount

>01-10........................... 100,000

>10-20............................200,000

>20-30............................400,000

>30-40............................500,000

>40...............................600,000


Hope some one can advise me.


Paul Yeo



1 Solution

Accepted Solutions
Anil_Babu_Samineni

May be this?

=If(Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE)  >0 and Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE)<10, '100,000',

If(Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE) >10 and Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE)<20, '200,000',

If(Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE) >20 and Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE)<30, '400,000',

If(Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE) >30 and Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE)<40, '500,000','600,000'))))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

5 Replies
paulyeo11
Master
Master
Author

My QVW

I forget to mentioned my previous table is looking below :-

Stock description .......... Average monthly sold amount.........On hand stock amount..........Inventory turn over Ratio

Omron Relay...................10.....................................................100........................................10

Anil_Babu_Samineni

May be this?

=If(Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE)  >0 and Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE)<10, '100,000',

If(Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE) >10 and Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE)<20, '200,000',

If(Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE) >20 and Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE)<30, '400,000',

If(Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE) >30 and Aggr(SUM(C_ONHAND_AMT),DESC_INVENTORY_TABLE)<40, '500,000','600,000'))))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
paulyeo11
Master
Master
Author

Hi Anil

Thank you very much for your help.

I don’t have my note book now , I think your expression I should put at dimension right ?

Why the expression you recommend , does not compute the inventory turn ratio ? I mean it should amount on hand divide by average monthly cost of goods sold ?

May I know the expression only put the on hand amount field right ? So that it will sum up the inventory turn ratio amount by class.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>

Anil_Babu_Samineni

I'm sorry, Not sure i understand

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
paulyeo11
Master
Master
Author

Hi Anil

After i play with your expression , it work as per my requirement :-

=If(Aggr(SUM(C_ONHAND_AMT)/sum(B_COGS),DESC_INVENTORY_TABLE)  >0 and Aggr(SUM(C_ONHAND_AMT)/sum(B_COGS),DESC_INVENTORY_TABLE)<10, dual('1-10',1),


If(Aggr(SUM(C_ONHAND_AMT)/sum(B_COGS),DESC_INVENTORY_TABLE) >10 and Aggr(SUM(C_ONHAND_AMT)/sum(B_COGS),DESC_INVENTORY_TABLE)<20, dual('10-20',2),


If(Aggr(SUM(C_ONHAND_AMT)/sum(B_COGS),DESC_INVENTORY_TABLE) >20 and Aggr(SUM(C_ONHAND_AMT)/sum(B_COGS),DESC_INVENTORY_TABLE)<30, dual('20-30',3),


If(Aggr(SUM(C_ONHAND_AMT)/sum(B_COGS),DESC_INVENTORY_TABLE) >30 and Aggr(SUM(C_ONHAND_AMT)/sum(B_COGS),DESC_INVENTORY_TABLE)<100, dual('30-100',4),


If(Aggr(SUM(C_ONHAND_AMT)/sum(B_COGS),DESC_INVENTORY_TABLE) >100 and Aggr(SUM(C_ONHAND_AMT)/sum(B_COGS),DESC_INVENTORY_TABLE)<2000, dual('100-2000',9)


)))))


Thank you