5 Replies Latest reply: Mar 9, 2017 2:43 AM by Rahul Pawar

# How to combine 2 Table into 1 ?

Hi All

I have 2 Expression for Table 1 & 2 as below , May i know how to combine both table into one ?

I try to do , but i don't know where to start. Hope some one can advise me.

Paul

Table 2 Expression :-

money(

SUM({<year = {'\$(=Max(year)-1)'}, month = {"<=\$(=IF(GetSelectedCount(month), Max({<year = {'\$(=Max(year))'}>}month), Num(Month(Today()))))"}>}\$(ColumnDim51)/\$(Columndim89)/1000)

, \$(vMoneyFormatK))

Table 1 Expression :-

if

(

match([Pls select 70 Dim],'rEVENUE'),

money(

SUM({<year = {"\$(=Max(year)-1)"},month={"<=\$(=month)"},\$(ColumnDim71)={\$(ColumnDim71)}>}Amount*1)/\$(Columndim89)/1000,

\$(vMoneyFormatK)),

if

(

match([Pls select 70 Dim],'sales'),

money(

sum({\$<year = {\$(=max(year)-1)}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>}\$(ColumnDim51)/\$(Columndim89)/1000),

\$(vMoneyFormatK)),

if

(

match([Pls select 70 Dim],'eXP'),

money(

SUM({<year = {"\$(=Max(year)-1)"},month={"<=\$(=month)"},\$(ColumnDim71)={\$(ColumnDim71)}>}Amount*-1)/\$(Columndim89)/1000,

\$(vMoneyFormatK)),

if

(

match([Pls select 70 Dim],'n_PRO'),

money(

SUM({<year = {"\$(=Max(year)-1)"},month={"<=\$(=month)"},\$(ColumnDim71)={\$(ColumnDim71)}>}Amount*1)/\$(Columndim89)/1000,

\$(vMoneyFormatK)),

))))

/*

if

(

match([Pls select 66 Dim],'rEVENUE'),

money(

SUM({<year = {"\$(=Max(year)-1)"},month={"<=\$(=month)"},\$(ColumnDim51)={\$(ColumnDim51)}>}Amount*1)/1000,

\$(vMoneyFormatK)),

if

(

match([Pls select 66 Dim],'sales'),

money(

sum({\$<year = {\$(=max(year)-1)}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>}\$(ColumnDim51)/\$(Columndim89)/1000)

, \$(vMoneyFormatK))

if

(

match([Pls select 66 Dim],'eXP'),

SUM({<year = {"\$(=Max(year)-1)"},month={"<=\$(=month)"},\$(ColumnDim51)={\$(ColumnDim51)}>}Amount*1)/1000,

if

(

match([Pls select 66 Dim],'n_PRO'),

SUM({<year = {"\$(=Max(year)-1)"},month={"<=\$(=month)"},\$(ColumnDim51)={\$(ColumnDim51)}>}Amount*1)/1000,

))))

/*

if

(

match([Pls select 66 Dim],'rEVENUE'),

SUM({<year = {"\$(=Max(year)-0)"},month={"<=\$(=month)"},\$(ColumnDim51)={\$(ColumnDim51)}>}Amount*1)/1000,

if

(

match([Pls select 66 Dim],'sales'),

sum({\$<year = {\$(=max(year)-0)}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>}\$(ColumnDim51)/\$(Columndim89)/1000),

if

(

match([Pls select 66 Dim],'eXP'),

SUM({<year = {"\$(=Max(year)-0)"},month={"<=\$(=month)"},\$(ColumnDim51)={\$(ColumnDim51)}>}Amount*1)/1000,

if

(

match([Pls select 66 Dim],'n_PRO'),

SUM({<year = {"\$(=Max(year)-0)"},month={"<=\$(=month)"},\$(ColumnDim51)={\$(ColumnDim51)}>}Amount*1)/1000,

))))

my QVW

• ###### Re: How to combine 2 Table into 1 ?

Hello Paul,

Trust that you are doing well!

Assuming that both the expressions are showing desired results, I have combined them into Table 2. Please refer the attached sample application. I still suspect there something which I missed during implementation.

Kindly review and let me know if this works.

Regards!

Rahul

• ###### Re: How to combine 2 Table into 1 ?

Hi Sir

I am fine , thank you for your sharing.

What i want to make the 2 expression into 1 expression.

So that my Table only have 1 Column. That can display all information .

Now it is 2 column.

My final table will be 3 column , column 1 LY Column 2 CY and Column 3 Vari.

Paul

• ###### Re: How to combine 2 Table into 1 ?

Hello Paul,

As per my understanding, I have combined two expressions into one separated by '|' (pipe). Please refer the attached sample expression.

Hope this will help.

Regards!

Rahul

• ###### Re: How to combine 2 Table into 1 ?

Hi Paul,

If you're trying to combine two expressions into one then you might need to create a calculated dimension and use pick / match (please see attached document)

Or, you can get really fancy and do something like the KPI report which I have blogged about here:

http://qlikanddirty.com/2017/01/06/custom-kpi-report-using-excel-template-and-variables/