Single Row Display

[Anonymous]

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

[MK_QSL]

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

Kindly provide sample data..

[sunny_talwar]

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

[MK_QSL]

Or you can use below calculated dimension, instead of Year

Aggr(Max(Year),KPI_NAME,MONTH_NAME)

[Anonymous]

its a straight table

[sunny_talwar]

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

[florentina_doga]

maybe this

[sunny_talwar]

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;