Merge four fields

[#1]

Hi

I'm new to qlikview and I've got this requirement in which I have four fields YTD_AC, YTD_BU, MNT_AC and MNT_BU coming straight from table and I want to merge them for the current year i.e. 2015 and all the months. Refer image for better understanding. How to implement that? Please help!

[Qrishna]

I guess we need more info on this.

could you please post a demo document?

[Anonymous]

one Expression should do it:

sum( YTD_AC)+sum(YTD_BU)+sum(MNT_AC)+sum(MNT_BU)

Dimension still is yearmonth

[ramasaisaksoft]

Hi Harshit,

First u need to create Inline Load for those 4 fileds

LOAD * INLINE [

id

ACTYTD

BUDMTH

BUDYTD

ACTMTH

];

WHEN U ARE WRITING expression u need to calculate  like

if(id='',

sum(),

if(id='',

sum(),....

[#5]

when you say merge, do you mean add the fields together?

[#6]

Here is the thing......the zone name, kpi name, kpi code, Unit, YTD_AC, YTD_BU, MTH_AC and MTH_BU, Month and year, all these values are coming from table. Now for a selected year and month I've to display the above report format....If I selected only year and not month then it should display the data of all the months for a given year as displayed above....and if I selected both a year and month...it should display data for only that year and month....What should I do..?

[petter]

It is typically what you can make with a Pivot Table in QlikView.

You should have a field for:

- Zone Name

- KPI Name

- KPI code

- Unit .... not sure if that is really necessary as a field... is it the % sign here?

- YearMonth

- MetricType: YTD_AC .... etc

First three should be dimensions on the left side as row labels

The last two should be dimensions that you drag up into column labels

[petter]

Here is a sample on how to do it:

[#9]

Yeah, Peter this is the exact format I need it for my report, how do I implement the YearMonth because year and month are two different dimensions here.

[petter]

You either have to create this field in you load script like this:

   Year( theDate ) & Num( Month( theDate ) , '00' ) AS YearMonth

OR

you can create a calculated dimension using the same logic - the dimension should be called YearMonth and expression is: Year( theDate ) & Num( Month( theDate ) , '00' )

Here I presume that you have a date field called theDate ... if it is two separate fields already you have:

theYear & Num( theMonth , '00' )