topic How do I accomplish Field data content Expansion in QlikView

How do I accomplish Field data content Expansion

Sat, 01 Dec 2012 15:09:22 GMT

Hi All,

I ll like to be able to do set analysis expresions based on the content of a field in a specific table. Pls see the example below:

Table

Description	Formula
Sales Turnover	10101, 10102, 10107
Admin Expenses	51010, 51023

The above table shows a template for calculating sales & admin expenses. Its simply by summing all amount in the transaction file (not shown) where AccountNo is equal to each of the accounts specified in the formular above.

How do I write an expression to accomplish this?

This is the code I could come up with, but its not working.

= Sum({$<GLActNo={$(formular)}>} Amount)

Thanks in advance.

Femi

Re: How do I accomplish Field data content Expansion

Sat, 01 Dec 2012 15:25:31 GMT

could you send sample file?

i'm not sure what is your data structure

Re: How do I accomplish Field data content Expansion

Sat, 01 Dec 2012 17:06:26 GMT

Thanks Pari,

Pls find attached d qv app.

What I m trying to achive is use the formular column in the Excel table to calculate the balances for each item in the straight/pivot table chart.

I m grateful to your anticipated assistance

Femi

Re: How do I accomplish Field data content Expansion

Sat, 01 Dec 2012 17:34:06 GMT

check this:

Sum({$<GLActNo={"$(=min(Formular))"}>} Amount)

but first of all fill all the values in excell becouse neither 10101001 nor 10101002 nor 10101005 dont have values and i could not check if my expression is correct

PS it is important if you write formular or Formular

Re: How do I accomplish Field data content Expansion

whiteline — Sun, 02 Dec 2012 19:30:13 GMT

Hi.

I guess you can't do it that way, becasue set expression is calculated once for the whole chart and then the expression is calculated over that set according to dimensions.

Re: How do I accomplish Field data content Expansion

Thu, 06 Dec 2012 23:52:00 GMT

Hi,

I ve tried the example above but its not working. I attach a clearer app so u can understand my goal.

My goal is to calculate the values of each row based on the account nos on the Formular column per row.

Thanks in advance

Femi

Re: How do I accomplish Field data content Expansion

Thu, 06 Dec 2012 23:53:24 GMT

I am grateful for any insight you may give in achieving my goal.

Pls see my reply to Pari above

Thanks in advance

Femi

Re: How do I accomplish Field data content Expansion

whiteline — Fri, 07 Dec 2012 07:20:04 GMT

Hi.

Why don't you just map the codes to the expences description?

Description Formula

Sales Turnover 10101

Sales Turnover 10107

Sales Turnover 10102

Admin Expenses 51010

Admin Expenses 51023

And then just use Sum().