How to display particular range values in table

[#1]

Hi All,

I want to display particular range value (from 31111 to 31121) under sales  AND

(from 41112 to 41118) under Materials column. Please find the attached QVW file for referance.

[anbu1984]

What is expected output?

[#3]

I want to display LedgerAccount column values from 31111 to 31121 under Sales column

& LedgerAccount column values from 41112 to 41118 under Materials column but i am unable to display

values under Materials column. Pls see attached qvw for referance.

For sales expression is

= if([Ledger Account]>='31111' AND [Ledger Account]<='31121',[Ledger Account])

For Material expression is

= if([Ledger Account]>='41112' AND [Ledger Account]<='41118',[Ledger Account])

[anbu1984]

Under sales you have 21 Accounts and under materials you have 7 accounts. Which material account you want to display for each of Sales accounts? Any rules to display?

[#5]

There is no relation of sales & Material Account. Only i want to display particular range values

under respective sale, Matrial columns for Cost center. SO sales expression which is written in Dimension

can be wriiten in expression part as well.I want to dispaly CostCenter wise Sales & Material column.

[anbu1984 · Accepted solution]

If(Index(vAllAcct,41112+(RowNo()-1)) And 41112+(RowNo()-1) <= 41118,41112+(RowNo()-1))

vAllAcct=Concat(DISTINCT {1} [Ledger Account],',')

[PrashantSangle]

Hi,

Try in Script level

Like

if([Ledger Account]>='31111' AND [Ledger Account]<='31121',[Ledger Account])  as Sales_Ledger_Account

and

if([Ledger Account]>='41112' AND [Ledger Account]<='41118',[Ledger Account]) as Material_Ledger_Account

Regards

[#8]

It works. Thanks anbu.

I have another requirement. I want to add new column 'Material Cost actual'

under this column i want to display

'Material cost BOM + Allocated cost in the ratio of Material cost BOM for ledgers 41111 & 41119 to 41130'.

eg: Under "Material cost column"value in orange 150 which is calculated 100+(100+1000*500) in red

     1000 is Sum of Ledger (41112 to 41118)

     500 is sum of Ledger range (41111 & 41119 to 41130)

     For referance pls refer same QVW file & help me on this

[anbu1984]

How did you derive 150? 100 + 100*(500/1000)??

[#10]

150 is calculated as 100 + 100*(500/1000)

150 under column Material Cost Actual for costcenter Canada

100 under column Material cost BOM for canada

1000 Sum of [Material cost BOM] column

500 is Sum of [Material cost BOM Actual] column (Assume 500 as eg. in screenshot)