Summing Across Rows where Column Name Matches Criteria

[#1]

I am new to Qlikview and I am working on an issue that I am stumped on. I would like to sum across rows (to create a variable) where the column name matches a certain pattern. For instance, one of my column names might be "CY.INCOME.APRIL" for income in April of the current year. There there will be a variable like this for each measure (such as income) for CY and PY (prior year) and each month of the year. I want to tell qlikview to sum across rows where the column name begins with "CY" and ends with the current month. I have found that current month is called by month(today()). Perhaps I could do len(month(today())) and tell it to look at this many characters at the end of each column name and match it to the current month to solve my problem on the back end, but I have no idea how to tell it to do that with column names. Can someone give me some direction?

Thanks for you time,

Josh

[swuehl]

I think you should look into the CROSSTABLE LOAD prefix:

The Crosstable Load

This will transform your many fields into two field, one field holding your former field names and one the values.

Then you can transform your former field names, creating new fields to flag CY / LY, payment type and month name.

Having done this transformation, it should be easy to sum your values filtering the records by month name, type,year.

[#3]

Thank you for your response. I think I should have been more clear. I don't have an income dimension or month dimension or a current year dimension. I have one field that I have pulled from a data cube that is currentyear.income.month that represents all of those things in one value. Basically I have this as columns

Region   CY.INCOME.JAN  PY.INCOME.FEB  CY.TAXES.SEPT and so forth and so on

I don't have a value field to put values in to correspond with the column name. or maybe I misunderstood how this is supposed to work. It seems in the example that "Sales" is a field that already exists in this person's QVD they are pulling from. Is there a way around this or to create one that you know of?

[swuehl]

Have a look at the output of this little script:

CROSS:

CROSSTABLE (FactFields, Value)

LOAD * INLINE [

Region,   CY.INCOME.JAN,  PY.INCOME.FEB,  CY.TAXES.SEPT

Europe, 10,20,30

];

RESULT:

LOAD Region,

  Subfield(FactFields,'.',1) as YearType,

  Subfield(FactFields,'.',2) as PaymentType,

  Subfield(FactFields,'.',3) as Month,

  Value

Resident CROSS;

DROP TABLE CROSS;