Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
If(Index(vAllAcct,41112+(RowNo()-1)) And 41112+(RowNo()-1) <= 41118,41112+(RowNo()-1))
vAllAcct=Concat(DISTINCT {1} [Ledger Account],',')
What is expected output?
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])
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?
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.
If(Index(vAllAcct,41112+(RowNo()-1)) And 41112+(RowNo()-1) <= 41118,41112+(RowNo()-1))
vAllAcct=Concat(DISTINCT {1} [Ledger Account],',')
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
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
How did you derive 150? 100 + 100*(500/1000)??
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)