RE:what wrong in my expression?

[Chanty4u]

hi all,

Am facing the issue in expression can any one correct me?  in pivot table

thanks in advance

i dnt hve master calender....

my script lik dis:

LOAD

    PRJ_NUMBER,

    PERIOD_NAME,

    BALANCE_ACTUAL,

    BALANCE_BUDGET,

    PERIOD_YEAR,

     Year(PERIOD_YEAR) as CYear,

     Month(PERIOD_YEAR) as CMonth,

     Date(PERIOD_YEAR) as CDate,

        PERIOD_NUM,

            PRJ_BU

FROM [lib://Management/MANAGEMENT_PACK.QVD]

(qvd);

current month-  Sum({$<Month = {$(=Max(Month))},Year = ,Quarter = ,Month = >} BALANCE_ACTUAL)

6months[til jun'15]  --  Sum({<Month = {"<=$(=max(Month, 6))"},Year = {$(=(max(Year)-1))}  >}BALANCE_ACTUAL)

Ytd[til dec'15-  Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} BALANCE_ACTUAL)

where am geting wrng values so kindly  help me out where am getting problem

thanks

suresh

[Anonymous]

this wud be your new expression with new Period_Number field:

=Sum({<Period_Number = {'$(=Max(Period_Number))'},Year = ,Quarter = ,Month = >} BALANCE_ACTUAL)

[Chanty4u]

am getting error while creating in  scriptng bro...

as calenderyear and calender mnths not found

[Anonymous]

Post a sample data, either QVD or Excel..

Dont put QVF as I dont have Qliksense

[Anonymous]

CalenderYear*12+month(CalenderMonth) as Period_Number

In your script, Replace CalenderYear with a field which contain values like 2014,2015 etc

Replace CalenderMonth with a field which contain values like Jan,Feb,Mar etc

[Chanty4u]

hi

PFA

this my qvd bro..

please help me on this

thanks

suresh

[jonathandienst]

Use the following script:

LOAD *,

  CYear * 12 + CMonth As PERIOD_NO;

LOAD

    PRJ_NUMBER,

    PERIOD_NAME,

    BALANCE_ACTUAL,

    BALANCE_BUDGET,

    PERIOD_YEAR,

    Year(PERIOD_YEAR) as CYear,

    Month(PERIOD_YEAR) as CMonth,

    Date(PERIOD_YEAR) as CDate,

    PERIOD_NUM,

    PRJ_BU

FROM [lib://Management/MANAGEMENT_PACK.QVD]

(qvd);

And then use the following expressions:

=Sum({$<Month = {"$(=Max(PERIOD_NO))"},CYear=, CQuarter=, CMonth=>} BALANCE_ACTUAL)

=Sum({$<Month = {">=$(AddMonths(Max(PERIOD_NO), -6))"}, CYear=, CQuarter=, CMonth=>} BALANCE_ACTUAL

=Sum({$<Month = {">=$(Max(PERIOD_NO) - 6)"}, CYear=, CQuarter=, CMonth=>} BALANCE_ACTUAL

[Chanty4u]

thanks jonathan,

As i used above script and expresn for actual

as it shwng same values there is no diff in values please chk below image

check for Actual and Actual HY u hve given dat expression

[tamilarasu]

Hi Suresh,

Your PERIOD YEAR field contains only year. You need to alter your script, so that the expressions will work. I just saw your QVD file and modified the script accordingly. You can use above set analysis, once you changed.

Data:
Load*,
'Q' & ceil(month(Date)/3) as Quarter;
LOAD PRJ_NUMBER,
     PERIOD_NAME,
     BALANCE_ACTUAL,
     BALANCE_BUDGET,
     PERIOD_YEAR,
     PERIOD_NUM,
     PRJ_BU,
     Makedate(PERIOD_YEAR,Month(Date#(PERIOD_NAME,'MMM-YY')),Date(PERIOD_NUM)) as Date,
     Month(Date#(PERIOD_NAME,'MMM-YY')) as Month,
     PERIOD_YEAR as Year
FROM
MANAGEMENT_PACK.QVD
(qvd);

Let me know.

[Chanty4u]

thnks, nagraj.

i am wrkng on this regarding  varience for  6mnth actual  abd budjet

and  variance for ytd  of actual and budjet..?

for how can i write the exprn in qliksense?

thanks

Suresh

[tamilarasu]

Sorry.. I didn't get a chance to use Qliksense. Did you try the above expressions!! You got a date, month, quarter, year fields. I guess it should work..