Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have a table 1 , which is working fine,
Inventory Turn over ratio........ On hand amount
1-10............................ 100,000
10-20............................200,000
30-100...........................400,000
... ...........................600,000
The last row is missing value , May i know how to make the last row display as "0 turn" ?
Hope some one can advise me.
Paul Yeo
May be add like this?
=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), Dual('0', 10))))))
my QVW
May be add like this?
=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), Dual('0', 10))))))
Hi Anil
Thank you very much it work fine.
By the way did you test your expression with my QVW doc ?
Paul Yeo
No, my analysis from here https://community.qlik.com/message/1414021#1414021