Solved: common month - Qlik Community

arulsettu · ‎2015-04-08

Hi,

I am having TD_month for actual amount and Bd_month for budget amount. i am trying to show actual and budget amount in pivot table. when i select TD_month the actual amount is correct. but the budget amount is wrong. same as when i select BD_month budget amount is correct and the actual amount is wrong

Actual and budget table having common filed.

so i did like this

month(Bd_Date) as TD_month

and concatenated with actual table. but no result.

plz suggest some advice.

thanks

jagan · ‎2015-04-08

Hi,

Try something like this

Trans_detail:

LOAD *, TD_year &'-'& TD_month as Yearmonth,

date(TD_DOC_DT) as Date;

LOAD TD_COMP_CODE & TD_MAIN_ACNT_CODE as %key,

TD_COMP_CODE,

TD_DOC_DT,

day(TD_DOC_DT) as TD_day,

month(TD_DOC_DT) as month,

Year(TD_DOC_DT) as year,

Date(MonthStart(TD_DOC_DT),'MMM-YYYY') as yearmonths,

'Q'& Ceil(Month(TD_DOC_DT)/3) as Td_Quarter,

if (day(TD_DOC_DT)<21 or (day(TD_DOC_DT)>=21 and month(TD_DOC_DT)='Jan') , month(TD_DOC_DT), month(addmonths(TD_DOC_DT,1))) as monthcheck,

if (day(TD_DOC_DT)<21 or (day(TD_DOC_DT)>=21 and month(TD_DOC_DT)='Jan') , 'Q' & ceil(month(TD_DOC_DT)/3), 'Q' & ceil(month(addmonths(TD_DOC_DT,1))/3)) as quartercheck,

TD_MAIN_ACNT_CODE,

TD_DRCR_FLAG,

TD_AMT_LC_1

FROM

"C:\ProgramData\QlikTech\Documents\New folder\QVD\[Trans Detail].qvd"

(qvd);

Temp:

CrossTable(Month, Bud_amount, 5)

LOAD COMP_CODE & LOB_CODE as %key,

BD_YEAR,

BD_TYPE,

BD_CR_DT,

BD_CR_UID,

BD_JAN,

BD_FEB,

BD_MAR,

BD_APR,

BD_MAY,

BD_JUN,

BD_JUL,

BD_AUG,

BD_SEP,

BD_OCT,

BD_NOV,

BD_DEC

FROM

(qvd);

Concatenate(Trans_detail)

LOAD

*,

month(date) as Bd_monthname;

LOAD

*,

Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') AS month,

MakeDate(BD_YEAR, Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')) AS date

Resident Temp;

DROP TABLE Temp;

Rename the date fields as same in both the tables.

Regards,

Jagan.

View solution in original post

PrashantSangle · ‎2015-04-08

Hi,

Can you provide sample app with sample data???

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Report Inappropriate Content · ‎2015-04-08

sum({$<BD_month=P(TD_month),TD_month=>} [Budget Amount])

Report Inappropriate Content · ‎2015-04-08

Hi Arul,

providing a detailed example and ideally a sample app would go a long way to troubleshooting this more quickly

Joe

arulsettu · ‎2015-04-08

Please find the app

Anonymous · ‎2015-04-08

sum({1<TD_month=>} [Budget Amount])

sum({1<BD_month=>} [Actual Amount])

jagan · ‎2015-04-08

Hi Arul,

Your Datamodel is not correct, I think Budget table is not necessary you can drop it, also concatenate the Trans_detail and Budget Calendar tables then you will have common date fields.

Regards,

Jagan.

arulsettu · ‎2015-04-08

Hi Jagan,

Should i rename the field

jagan · ‎2015-04-08

Hi,

Make the name of date fields same in both the tables.

Data:

LOAD

Value,

Date,

Month,

Year,

'

',

'Actual' AS Flag

FROM Trans_detail;

Concatenate(Data)

LOAD

Value AS Budget,

Date,

Month,

Year,

'

',

'Budget' AS Flag

FROM Budget;

Hope this helps you.

Regards,

Jagan.

arulsettu · ‎2015-04-08

Hi Jagan,

I dropped the Budget table and conccatenated Budget_calendar table with Trans_detail. budget amount become zero.

please can you tell me why?

Thanks

common month

QlikView App Dev

Related Topics