Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

How to make missing value at dimension display some charter like 0 turn ?


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



1 Solution

Accepted Solutions

Re: How to make missing value at dimension display some charter like 0 turn ?

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

4 Replies
paulyeo11
Valued Contributor II

Re: How to make missing value at dimension display some charter like 0 turn ?

my QVW

Re: How to make missing value at dimension display some charter like 0 turn ?

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

paulyeo11
Valued Contributor II

Re: How to make missing value at dimension display some charter like 0 turn ?

Hi Anil

Thank you very much it work fine.

By the way did you test your expression with my QVW doc ?

Paul Yeo

Re: How to make missing value at dimension display some charter like 0 turn ?

Community Browser