topic Re: Last year current month in QlikView

Last year current month

n1ef5ng1 — Fri, 19 Jul 2013 17:46:58 GMT

I have a problem displaying out the last month. When a user select April 2013, I will have two expression that show April 2013 and April 2012. It will take the current month.

This is my expression for both of them

Label : Only(Month) and Only(Year)-1

Sum(

{$<Line_Code={'ADV','MAL','AMT'},Derived_Status={'IMPT','EXPT'},Derived_Container_Class_Name={'LADEN'},Month_Id={"<= $(=Max(Month_Id))"},Year ={$(=Max(Year)-1)},Month=, Month_Year = >}TEU

ONLY(Month) & ' ' & ONLY(Year

)

sum({<Line_Code={'ADV','MAL','AMT'},Derived_Status={'IMPT','EXPT'},Derived_Container_Class_Name={'LADEN'},Month_Id={$(=Max(Month_Id))},Year = ,Month=, Month_Year = >} TEU)

For Only(Month) and Only(Year), current year. i managed to get the value correctly,

for Only(Month) and Only(Year)-1, I get a YTD of Jan - Apr 2012 instead. I am actuallly looking for Apr 12 current month instead.

why is this so, thanks

Re: Last year current month

tresB — Fri, 19 Jul 2013 18:02:22 GMT

Remove '<' from you first expression.

n1ef5ng1 wrote:

Sum(
{$<Line_Code={'ADV','MAL','AMT'},Derived_Status={'IMPT','EXPT'},Derived_Container_Class_Name={'LADEN'},Month_Id={" < = $(=Max(Month_Id))"},Year ={$(=Max(Year)-1)},Month=, Month_Year = >}TEU

Re: Last year current month

n1ef5ng1 — Fri, 19 Jul 2013 18:07:32 GMT

what do u mean?

Re: Last year current month

tresB — Fri, 19 Jul 2013 18:12:52 GMT

there is a '<' symbol before the smiling face(i inserted), remove that and try.

Re: Last year current month

n1ef5ng1 — Fri, 19 Jul 2013 18:18:11 GMT

doesn work though, my figure all became 0 after removing >

Re: Last year current month

tresB — Fri, 19 Jul 2013 18:21:51 GMT

Sum(
{$<Line_Code={'ADV','MAL','AMT'},Derived_Status={'IMPT','EXPT'},Derived_Container_Class_Name={'LADEN'},Month_Id={" $(=Max(Month_Id))"},Year ={$(=Max(Year)-1)},Month=, Month_Year = >}TEU

Try copy paste the above

Update: sorry, missed the last parenthesis ')', please add that and try.

Re: Last year current month

n1ef5ng1 — Fri, 19 Jul 2013 18:25:40 GMT

still zero value, i not sure why. this coding should work too.

why is it my previous coding will give me a ytd

Re: Last year current month

tresB — Fri, 19 Jul 2013 18:31:42 GMT

if you put Month_Id= {"<= $(=Max(Month_Id))"} ..... that means month would considred if that is LESS THAN or EQUAL TO, hence Jan to Apr all months. that is why if you remove the '<=' part it should give you the desired result.

hope this helps.

Re: Last year current month

n1ef5ng1 — Fri, 19 Jul 2013 19:00:49 GMT

okay learnt something new thanks.

really clueless on why is the value not showing

Re: Last year current month

tresB — Fri, 19 Jul 2013 19:03:30 GMT

Can you share a sample app?

Re: Last year current month

n1ef5ng1 — Fri, 19 Jul 2013 19:05:32 GMT

ok i will try,

but anywhere this is my code,

Sum(
{$<Line_Code={'ADV','MAL','AMT'},Derived_Status={'IMPT','EXPT'},Derived_Container_Class_Name={'LADEN'},Month_Id={" $(=Max(Month_Id))"},Year ={$(=Max(Year)-1)},Month=, Month_Year = >}TEU

/
Aggr(
NODISTINCT
   Sum(
    {<Line_Code={'ADV','MAL','AMT'},Derived_Status={'IMPT','EXPT'},Derived_Container_Class_Name={'LADEN'},Month_Id={"$(=Mac(Month_Id))"},Year ={$(=Max(Year)-1)}, Month=, Month_Year = >}
   TEU),Year
)
      )

Re: Last year current month

n1ef5ng1 — Fri, 19 Jul 2013 19:11:34 GMT

ok i will try,

but anywhere this is my code,

Sum(
{$<Line_Code={'ADV','MAL','AMT'},Derived_Status={'IMPT','EXPT'},Derived_Container_Class_Name={'LADEN'},Month_Id={" $(=Max(Month_Id))"},Year ={$(=Max(Year)-1)},Month=, Month_Year = >}TEU

Re: Last year current month

Sokkorn — Sat, 20 Jul 2013 02:27:43 GMT

Hi,

Look on you expression, found something wrong

n1ef5ng1 wrote:
ok i will try,
but anywhere this is my code,

Sum(
{$<Line_Code={'ADV','MAL','AMT'},Derived_Status={'IMPT','EXPT'},Derived_Container_Class_Name={'LADEN'},Month_Id={" $(=Max(Month_Id))"},Year ={$(=Max(Year)-1)},Month=, Month_Year = >}TEU ) // Missing bracket
/
Aggr(
NODISTINCT
   Sum(
    {<Line_Code={'ADV','MAL','AMT'},Derived_Status={'IMPT','EXPT'},Derived_Container_Class_Name={'LADEN'},Month_Id={"$(=Mac(Month_Id))"},Year ={$(=Max(Year)-1)}, Month=, Month_Year = >}
   TEU),Year
)
      ) // Should take it out

Now let try:

Sum({$<Line_Code={'ADV','MAL','AMT'},Derived_Status={'IMPT','EXPT'},Derived_Container_Class_Name={'LADEN'},Month_Id={$(=Max(Month_Id))},Year ={$(=Max(Year)-1)},Month=, Month_Year = >} TEU)

Aggr(

NODISTINCT

Sum(

{<Line_Code={'ADV','MAL','AMT'},Derived_Status={'IMPT','EXPT'},Derived_Container_Class_Name= {'LADEN'},Month_Id={$(=Max(Month_Id))},Year ={$(=Max(Year)-1)}, Month=, Month_Year = >} TEU)

,Year)

Regards,

Sokkorn

Re: Last year current month

n1ef5ng1 — Mon, 22 Jul 2013 00:24:46 GMT

the whole expression is invalid when i paste the code that you gave.

Re: Last year current month

laujerry — Mon, 22 Jul 2013 06:30:51 GMT

have u tried decomposing your expressions into short one first

like showing the Sum part and Aggr part in separate expressions, removing part of the set conditions?

can u still get the expected result?

Re: Last year current month

n1ef5ng1 — Mon, 22 Jul 2013 06:33:24 GMT

i tried, sum was correct. Aggr actually display YTD for 2013 and not last year, 2012

Re: Last year current month

nizamsha — Mon, 22 Jul 2013 06:39:44 GMT

if u want lastyear current month and current date

u can write like in ur expression

year(today())-1,month(today()),day(today())

this will give lastyear,currentmonth, currentdate

eg::

22:7:2017 instead of this it will give

2:7:2012