How to filter a field when its values are distributed into several table fields?

[#1]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.02.89.41/Monthly-Sales-Targets.qvw]

Hi Guys,

I need help to solve the following situation. Please see the attachment.

I have a table with Sales Target for each Customer, but the table has the values for each month in a different field.

I need to be able to filter the sales target by Customer, year and month.

How can I work it around?

Thanks in advance,

Aldo.

[Miguel_Angel_Baeyens · Accepted solution]

Hello Aldo,

You may try something like

MonthlyTargets:crosstable (month, sales, 2) loadYear, Customer, JanuaryQuant, FebruaryQuant from MonthlyTargets.qvd (qvd);

Is this what you are looking for?

[#3]

Hi Miguel,

I solved it, re-creating the table as below, but I think it is not the best solution. Let me try your suggestion.

Thanks,

Aldo.

MonthlyTargets:

let MonthNo = 1;

Do while MonthNo < 13

load

Customer,

Year,

'$(MonthNo)' as Month,

if('$(MonthNo)'=1, JanuaryQuant,

if('$(MonthNo)'=2, FebruaryQuant)

) as Quant

from ...MonthlyTargets.qvd (qvd);

Let MonthNo = MonthNo+1;

loop;