common month

[arulsettu]

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 · Accepted solution]

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.

[arulsettu]

Hi Jagan,

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

thanks

[jagan]

Hi,

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

Regards,

Jagan.

[arulsettu]

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

[jagan]

Hi,

Can you attach your latest file and expected output?

Regards,

jagan.

[arulsettu]

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

thanks you all

[arulsettu]

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

[jagan]

Hi,

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

Regards,
Jagan.