topic Re: How to display particular range values in table in QlikView

How to display particular range values in table

Tue, 30 Dec 2014 10:47:26 GMT

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.

Re: How to display particular range values in table

anbu1984 — Tue, 30 Dec 2014 11:10:20 GMT

What is expected output?

Re: How to display particular range values in table

Tue, 30 Dec 2014 11:38:21 GMT

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

Re: How to display particular range values in table

anbu1984 — Tue, 30 Dec 2014 11:42:13 GMT

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?

Re: How to display particular range values in table

Tue, 30 Dec 2014 11:53:06 GMT

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.

Re: How to display particular range values in table

anbu1984 — Tue, 30 Dec 2014 12:20:10 GMT

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

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

Re: How to display particular range values in table

PrashantSangle — Tue, 30 Dec 2014 12:29:41 GMT

Hi,

Try in Script level

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

Re: How to display particular range values in table

Wed, 31 Dec 2014 12:11:58 GMT

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

Re: How to display particular range values in table

anbu1984 — Wed, 31 Dec 2014 12:56:01 GMT

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

Re: How to display particular range values in table

Fri, 02 Jan 2015 10:23:04 GMT

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)

Re: How to display particular range values in table

anbu1984 — Fri, 02 Jan 2015 10:31:41 GMT

Which field in your model is Material Cost BOM?

Re: How to display particular range values in table

Fri, 02 Jan 2015 10:44:49 GMT

There is no field in data model as Material cost BOM

MAterial cost BOM is Ledger values From 41112 to 41118

Material Cost BOM is defined in expression i.e

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

We need to write expression for new column(Material Cost Actual) to calculate below result

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