Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

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

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

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

5 Replies
paulyeo11
Valued Contributor II

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

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 ?

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

paulyeo11
Valued Contributor II

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

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 ?

I'm sorry, Not sure i understand

paulyeo11
Valued Contributor II

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

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



Community Browser