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

[paulyeo11]

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

[paulyeo11]

My Qvw

[Anil_Babu_Samineni]

Perhaps this in your measure?

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

[paulyeo11]

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

[Anil_Babu_Samineni · Accepted solution]

PFA

[paulyeo11]

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 ?

[Anil_Babu_Samineni]

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]

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

[Anil_Babu_Samineni]

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

Best