How to Calculate concatenated strings in Set Analysis

qlik107 — Wed, 19 Nov 2014 15:25:24 GMT

I have a GP Target table with Targets as Month1, Month2,..........,Month12 . I want to calculate YTD for specified consultant for Month11. So it must sum the values from( Month1+Month2............+Month11). I have achieved to calculate target for current Month. But no success on YTD. I have attached the GP Target Excel File also . So please provide any suggestions ASAP.

Re: How to Calculate concatenated strings in Set Analysis

ali_hijazi — Wed, 19 Nov 2014 15:32:22 GMT

you need to use cross table so that you transform month1, month2 columns into rows

then all your months will be under one column named month

as per the excel sheet that you sent

your script will be somehting like this:

test:

LOAD [Emp ID],

Name,

Level,

Location,

[115.71],

[0.15],

A1,

B1,

C1,

A2,

B2,

C2,

F28,

[Our Targets],

Var,

Var%,

AF,

AG,

Month1,

Month2,

Month3,

Month4,

Month5,

Month6,

Month7,

Month8,

Month9,

Month10,

Month11,

Month12

FROM

[folder\Target cascade template.xlsx]

(ooxml, embedded labels, table is [IC spread]);

CrossTable(Month,Value,21)

load * resident test;

drop Table test;

topic Re: How to Calculate concatenated strings in Set Analysis in QlikView

How to Calculate concatenated strings in Set Analysis

Re: How to Calculate concatenated strings in Set Analysis