topic Re: Single Row Display in QlikView

Single Row Display

Anonymous — Thu, 28 Apr 2016 13:40:01 GMT

Current Situation

We have 2 rows displaying the data.

Expectation is two have the info in one row.

2016 amount of clean.. February 1789711.0200 1300993.9194.

I have tried multiple tries with set analysis, but no luck.

thanks

Manish Madan

Re: Single Row Display

MK_QSL — Thu, 28 Apr 2016 13:41:15 GMT

How come... Year is different for both lines...

Kindly provide sample data..

Re: Single Row Display

sunny_talwar — Thu, 28 Apr 2016 13:41:53 GMT

You can do this in multiple ways, but if making changes in the script is not an issue, then I would suggest using As-ofTable concept (The As-Of Table‌)

UPDATE: Are we looking at a table box object or a straight Table? Finding it hard to know if this is table box or straight table

Re: Single Row Display

MK_QSL — Thu, 28 Apr 2016 13:44:04 GMT

Or you can use below calculated dimension, instead of Year

Aggr(Max(Year),KPI_NAME,MONTH_NAME)

Re: Single Row Display

Anonymous — Thu, 28 Apr 2016 13:46:39 GMT

its a straight table

Re: Single Row Display

sunny_talwar — Thu, 28 Apr 2016 13:57:26 GMT

Script:

Table:

LOAD *,

Year(DATE) as YEAR,

Month(DATE) as MONTH,

MonthName(DATE) as MONTHYEAR;

LOAD * Inline [

KPI_NAME, DATE, VALUE

Amount of Clean, 01/02/2015, 1300993.9194

Amount of Clean, 01/02/2016, 1789711.0200

];

[AS-OF TABLE]:

LOAD MONTHYEAR as REPORT_MONTHYEAR,

MONTHYEAR,

'CY' as FLAG

Resident Table;

Concatenate([AS-OF TABLE])

LOAD MONTHYEAR as REPORT_MONTHYEAR,

MonthName(AddYears(MONTHYEAR, -1)) as MONTHYEAR,

'PY' as FLAG

Resident Table;

Straight Table

Dimensions

REPORT_MONTHYEAR

KPI_NAME

Expressions:

1) =Sum({<FLAG = {'CY'}>}VALUE)

2) =Sum({<FLAG = {'PY'}>}VALUE)

Data model

Re: Single Row Display

florentina_doga — Thu, 28 Apr 2016 13:59:22 GMT

maybe this

Re: Single Row Display

sunny_talwar — Thu, 28 Apr 2016 14:00:33 GMT

Or this to get Month and Year separately

Table:

LOAD *,

MonthName(DATE) as MONTHYEAR;

LOAD * Inline [

KPI_NAME, DATE, VALUE

Amount of Clean, 01/02/2015, 1300993.9194

Amount of Clean, 01/02/2016, 1789711.0200

];

[AS-OF TABLE]:

LOAD MONTHYEAR as REPORT_MONTHYEAR,

Year(MONTHYEAR) as REPORT_YEAR,

Month(MONTHYEAR) as REPORT_MONTH,

MONTHYEAR,

'CY' as FLAG

Resident Table;

Concatenate([AS-OF TABLE])

LOAD MONTHYEAR as REPORT_MONTHYEAR,

Year(MONTHYEAR) as REPORT_YEAR,

Month(MONTHYEAR) as REPORT_MONTH,

MonthName(AddYears(MONTHYEAR, -1)) as MONTHYEAR,

'PY' as FLAG

Resident Table;