topic Re: Help in QlikView

Help

panipat1990 — Mon, 26 Jan 2026 18:19:17 GMT

Hi Team,

I am stuck in one requirement which is not able to implement by me. I need your help here.

my requirement is :-

we have four fields in our spreadsheet:-

1. Division

2. Year

3. Revenue

4. Category

in Division field , we have 4 values(CORP,ESM,SAS,ESC) And Year value is {2014,2015 and 2016} with revenue amount is given.

But For "ESM Division" , we are not getting the "2016" year and revenue value data from spreadsheet data. And i want to show the revenue amount for "ESM Division" for 2016 year in qlikview which is the cumulative Revenue value of 2015 and 2014.

And when i select ESM Division as selection filter, then In Bar Chart ..Three BAR Piece should be shown :- one is for 2014 , 2nd is for 2015 and third is for 2016(which would be the cumulative revenue amount of 2014 and 2015 year).

Also i have attached the spreadsheet for the same data.

please check and help me over this.

Thanks

Gourav

Re: Help

trdandamudi — Thu, 23 Jun 2016 19:39:35 GMT

May be as attached:

Data:

LOAD Year,

Division,

Revenue,

Re: Help

panipat1990 — Sat, 02 Jul 2016 18:57:20 GMT

This is static code . Can you tell me how tto make it dynamic

Re: Help

vinieme12 — Sat, 02 Jul 2016 21:13:21 GMT

Hi Gourav,

Please see attached QVW , hopefully this helps you.

TAB_MAX_YEAR:

LOAD max(Year) as MAXYEAR

FROM

(ooxml, embedded labels, table is Sheet1);

Let vMaxYear = NUM(Peek('MAXYEAR',0,'TAB_MAX_YEAR'));

LOAD Year,

Division,

Category,

Revenue

FROM

(ooxml, embedded labels, table is Sheet1)

Where Year < $(vMaxYear);

CONCATENATE

LOAD $(vMaxYear) as Year,

Division,

Category,

SUM(Revenue) as Revenue

FROM

(ooxml, embedded labels, table is Sheet1)

Where Year < $(vMaxYear)

Group By

Division,

Category;

Re: Help

Digvijay_Singh — Sun, 03 Jul 2016 03:04:08 GMT

May be like this -

Re: Help

panipat1990 — Wed, 06 Jul 2016 09:50:08 GMT

Hi Sir,

Suppose in place of revenue we have employee id. and count of employee id for max year show 1..But i want to cumulative count of 2014 and 2015 employee id..how is it possible...kindly help me for solving this requirement.

Re: Help

vinieme12 — Wed, 06 Jul 2016 10:38:58 GMT

I've made my own sample data, hope it helps.

TAB_MAX_YEAR:

LOAD max(Year) as MAXYEAR

FROM

(ooxml, embedded labels, table is Sheet1);

Let vMaxYear = NUM(Peek('MAXYEAR',0,'TAB_MAX_YEAR'));

EMPID_COUNT:

LOAD Distinct EmpID,

Year,

Division,

Category,

1 as EMPCOUNT

FROM

(ooxml, embedded labels, table is Sheet1)

Where Year < $(vMaxYear);

CONCATENATE

LOAD $(vMaxYear) as Year,

Division,

Category,

SUM(EMPCOUNT) as EMPCOUNT

RESIDENT EMPID_COUNT

Where Year < $(vMaxYear)

Group By

Division,

Category;

Re: Help

panipat1990 — Wed, 06 Jul 2016 17:56:01 GMT

Hi Sir,

This is sum of employee id...But i want to count of employee id..

Re: Help

panipat1990 — Wed, 06 Jul 2016 18:01:18 GMT

In Above Your Example Division corp against employee id in 2016 cumulative count of 2014 or 2015....it should be 21 for corp division.selecting year 2016

Re: Help

vinieme12 — Wed, 06 Jul 2016 18:48:39 GMT

Ok, now i think i know what you need.

Please refer to the original attachment I provided for summarizing revenue and try replicating the same for Emp, the concept is the same.

If you get stuck please post a sample app or data in a new thread.