topic Work with variable as Field Value in QlikView

Work with variable as Field Value

Fri, 09 Jul 2010 14:18:51 GMT

Hi,

I have a question.

In a table I got 12 fields, WKG001, WKG002, ..... up to WKG012 (containing values.)

Each field represents the global result of financial postings, and it builds month by month, from WKG001 to WKG012

So this means in field WKG001 you can find the results of Month Januari, in WKG002 = Februari ...... WKG012 = December.

I also have another field, wich contains the year of posting.

So the combination of year & WKG will give you the posting of year & month.

How can I make a 'selector' so the customer can choose his from/to period(s)???

I my chart I got dimension Order and Year, and in the expressions the 12 seperate SUM(WKG)-fields.

Many thanks for your response.

wizardo — Fri, 09 Jul 2010 22:48:16 GMT

hmmm

i think you will need to change the format of the table using the crosstable KEYWORD

so instead of having one row of data per each order per each year with 12 fields (one field per month)

you will have 12 rows of data per each order per each year (one row per month)

with the fields:

Order Year Month Amount

then you can select a month too (and even connect it to a master calander)

you can check the syntax for the "CrossTable" keyword in the manual

Mansyno

Tue, 13 Jul 2010 09:59:26 GMT

Hi, thanks for your suggestion, but this will not work.

I now have allready a table of 7,5 million records in my table, and growing every day.

Turning the columns into rows will multiply this qty of lines x 12.

This is too much.

But I found an alternative which works...

I made a small inline table with 12 columns, containing Months & Period ='1' or '0', and linked this on my table via Key Month/Period

Depending on wich 'Month' you select in this inline table, it will say wich 'period' x0 and x1.

Month 1 will give you 'x1', 'x0', 'x0', 'x0', 'x0', .....

Month 2 will give you 'x1', 'x1', 'x0', 'x0', 'x0', ......

If I do (KF1 x 'month1') + (KF2 x 'month2') + .......... I got a nice result

e.g.

Month 1 = (10 x 1) + (20 x 0) + (30 x 0) + ....... = 10

Month 2 = (10 x 1) + (20 x 1) + (30 x 0) + ....... = 30

Result: Month 1 = Value 10, Month 2 = Value 30 --> correct

For the user, the result is shown as like you would select a Period in a std calendar and getting the calculations in your Chart.