topic Re: common month in QlikView

common month

arulsettu — Wed, 08 Apr 2015 06:23:12 GMT

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

Re: common month

PrashantSangle — Wed, 08 Apr 2015 06:27:32 GMT

Hi,

Can you provide sample app with sample data???

Regards

Re: common month

Wed, 08 Apr 2015 06:29:13 GMT

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

Re: common month

Wed, 08 Apr 2015 06:32:38 GMT

Hi Arul,

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

Joe

Re: common month

arulsettu — Wed, 08 Apr 2015 06:42:08 GMT

Please find the app

Re: common month

Anonymous — Wed, 08 Apr 2015 06:51:23 GMT

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

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

Re: common month

jagan — Wed, 08 Apr 2015 06:51:51 GMT

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.

Re: common month

arulsettu — Wed, 08 Apr 2015 06:57:55 GMT

Hi Jagan,

Should i rename the field

Re: common month

jagan — Wed, 08 Apr 2015 07:12:11 GMT

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.

Re: common month

arulsettu — Wed, 08 Apr 2015 07:12:33 GMT

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

Re: common month

jagan — Wed, 08 Apr 2015 07:18:00 GMT

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.

Re: common month

arulsettu — Wed, 08 Apr 2015 07:30:32 GMT

Hi Jagan,

Budget amount become zero if i select year and month. i dont know why

thanks

Re: common month

jagan — Wed, 08 Apr 2015 07:38:28 GMT

Hi,

Rename the Year and month in Budget table same as Trans_detail table.

Regards,

Jagan.

Re: common month

arulsettu — Wed, 08 Apr 2015 07:39:58 GMT

thanks jagan i did but the budget amount figure is incorrect

sum({<BD_TYPE={'GEN_ADMIN_EXP'}>}Bud_amount)

is there anything wrong with cross table

Re: common month

jagan — Wed, 08 Apr 2015 08:03:15 GMT

Hi,

Can you attach your latest file and expected output?

Regards,

jagan.

Re: common month

arulsettu — Wed, 08 Apr 2015 08:04:54 GMT

Thank you very much jagan i made some chages now i got it

thanks you all

Re: common month

arulsettu — Wed, 08 Apr 2015 09:10:31 GMT

Hi Jagan,

one doubt for year 2014 the budget amount is showing and for 2013 its only zero. i checked the database values are there for 2013. can you please tell me why

thanks

Re: common month

jagan — Wed, 08 Apr 2015 09:12:33 GMT

Hi,

Check whether the data is available in Dashboard, I think it is not there.

Regards,
Jagan.