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

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

paulyeo11 — Tue, 09 Jan 2018 02:50:49 GMT

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

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

paulyeo11 — Tue, 09 Jan 2018 02:53:30 GMT

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

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

Anil_Babu_Samineni — Tue, 09 Jan 2018 12:53:35 GMT

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

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

paulyeo11 — Tue, 09 Jan 2018 13:06:29 GMT

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>

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

Anil_Babu_Samineni — Tue, 09 Jan 2018 13:27:20 GMT

I'm sorry, Not sure i understand

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

paulyeo11 — Wed, 10 Jan 2018 06:08:25 GMT

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