topic Re: RE:what wrong in my expression? in QlikView

RE:what wrong in my expression?

Chanty4u — Mon, 09 Nov 2015 07:45:44 GMT

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

Re: RE:what wrong in my expression?

avinashelite — Mon, 09 Nov 2015 07:48:30 GMT

on a high level , double quotes are missing in the first expression

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

Re: RE:what wrong in my expression?

Chanty4u — Mon, 09 Nov 2015 07:51:07 GMT

thanks avinash.

but if do that also doesnt chnge in values? shwng same values

thanks

Suresh

Re: RE:what wrong in my expression?

avinashelite — Mon, 09 Nov 2015 07:55:42 GMT

can you please share you app and the count your looking for ? because it would be hard to identify the error without seeing the file

Re: RE:what wrong in my expression?

Anonymous — Mon, 09 Nov 2015 07:58:10 GMT

Try this:

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

Make sure Month is interger as Max(Month) returns integer

Re: RE:what wrong in my expression?

nagarjuna_kotha — Mon, 09 Nov 2015 08:00:47 GMT

Like this ?

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

Re: RE:what wrong in my expression?

Chanty4u — Mon, 09 Nov 2015 08:06:24 GMT

no luck... nagarjuna, & balraj

giving same values

Re: RE:what wrong in my expression?

Anonymous — Mon, 09 Nov 2015 08:11:21 GMT

Did you check whether you Month Field is having String or Numeric...

Becoz max(Month) would return an interger..

If you Month Field is having values like Jan, Feb, Mar etc where max is Mar.

Now, IF I would do ,max(Month), I wud get 3 which is not matching with Mar..

Did you get??

Re: RE:what wrong in my expression?

Chanty4u — Mon, 09 Nov 2015 08:16:57 GMT

i hve like string only..

like jan,feb...

Re: RE:what wrong in my expression?

Anonymous — Mon, 09 Nov 2015 08:20:23 GMT

Then It would not work.

Create a numeric field say, Period_Number at script like in Calender table:

CalenderYear*12+month(CalenderMonth) as Period_Number

and then try..with this new field, it wud surely work

Re: RE:what wrong in my expression?

Anonymous — Mon, 09 Nov 2015 08:21:41 GMT

this wud be your new expression with new Period_Number field:

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

Re: RE:what wrong in my expression?

Chanty4u — Mon, 09 Nov 2015 08:27:06 GMT

am getting error while creating in scriptng bro...

as calenderyear and calender mnths not found

Re: RE:what wrong in my expression?

Anonymous — Mon, 09 Nov 2015 08:40:49 GMT

Post a sample data, either QVD or Excel..

Dont put QVF as I dont have Qliksense

Re: RE:what wrong in my expression?

Anonymous — Mon, 09 Nov 2015 08:42:26 GMT

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

Re: RE:what wrong in my expression?

Chanty4u — Mon, 09 Nov 2015 08:48:40 GMT

PFA

this my qvd bro..

please help me on this

thanks

suresh

Re: RE:what wrong in my expression?

jonathandienst — Mon, 09 Nov 2015 08:56:49 GMT

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

Re: RE:what wrong in my expression?

Chanty4u — Mon, 09 Nov 2015 09:10:16 GMT

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

Re: RE:what wrong in my expression?

tamilarasu — Mon, 09 Nov 2015 09:19:54 GMT

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.

Re: RE:what wrong in my expression?

Chanty4u — Mon, 09 Nov 2015 11:24:00 GMT

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

Re: RE:what wrong in my expression?

tamilarasu — Mon, 09 Nov 2015 12:16:42 GMT

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..