Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

How to add one more condition to =if( [C_ONHAND_AMT]>1,[product code]) ?

Hi All


I have a table which is working fine.

I need to make the table only display those stock Number of Turn > 10

Now it display all stock Number of turn > 0.


My stock turn over expression :-


SUM(C_ONHAND_AMT)/sum(B_COGS)


My dimension expression now as below , working fine ::-


=if( [C_ONHAND_AMT]>1,[product code])


In order only list those stock Numberof turn >10


I need to add below expression to existing dimension expression  :-


SUM(C_ONHAND_AMT)/sum(B_COGS)>10


So the overall new expression for dimension should be :-


=if( [C_ONHAND_AMT]>1 and C_ONHAND_AMT/sum(B_COGS)>10,[product code])


But it does not work.


Paul



 

 

 

1 Solution

Accepted Solutions

Re: How to add one more condition to =if( [C_ONHAND_AMT]>1,[product code]) ?

PFA

8 Replies
paulyeo11
Valued Contributor II

Re: How to add one more condition to =if( [C_ONHAND_AMT]>1,[product code]) ?

My Qvw

Re: How to add one more condition to =if( [C_ONHAND_AMT]>1,[product code]) ?

Perhaps this in your measure?

Sum({<C_ONHAND_AMT = {'>1'}, [product code] = {"=SUM(C_ONHAND_AMT)/sum(B_COGS)>10"}>} COS)

paulyeo11
Valued Contributor II

Re: How to add one more condition to =if( [C_ONHAND_AMT]>1,[product code]) ?

Hi Anil

Thank you for your fast reply.

it does not work.

i need to display the top 8 row .

I cannot set the max number = 8 , as other brand of listing is not top 8 row , it depend on ratio.

Paul

Re: How to add one more condition to =if( [C_ONHAND_AMT]>1,[product code]) ?

PFA

paulyeo11
Valued Contributor II

Re: How to add one more condition to =if( [C_ONHAND_AMT]>1,[product code]) ?

Hi Anil

wow i see that you can control all the number very well.

May i know ,why filter by dimension for both condition it will not work ?

Re: How to add one more condition to =if( [C_ONHAND_AMT]>1,[product code]) ?

I don't prefer Calc. Dimension due to performance in UI. Which is not a good practice. But we can force until unless not possible in set expression. Can you try this?

=if([C_ONHAND_AMT]>1 and Aggr((Sum(C_ONHAND_AMT)/sum(B_COGS)), [product code])>10, [product code])

paulyeo11
Valued Contributor II

Re: How to add one more condition to =if( [C_ONHAND_AMT]>1,[product code]) ?

Hi Anil

Your Dimension expression work fine :-

=if([C_ONHAND_AMT]>1 and Aggr((Sum(C_ONHAND_AMT)/sum(B_COGS)), [product code])>10, [product code])

it also give me missing value.

it save me time a lot. i Prefer filter by dimension , easy to maintenance.

Paul Yeo

Re: How to add one more condition to =if( [C_ONHAND_AMT]>1,[product code]) ?

Yes, Using calc. dimension nulls may produce. To get rid you have to suppress null value from Dimension ..

Best

Community Browser