topic Re: MTD not working in QlikView

MTD not working

Thu, 27 Sep 2012 07:19:25 GMT

Hi all,

Iam wroung some where please do let me know

I am just calculation YTD and MTD using set analysis

here is my expression

=if(Category='receivable',sum({<YEAR={'<=$(=MAX(YEAR))'},MONTH={'<=$(=MAX(MONTH))'}>} $(EXP)))

here if YTD is working fine,but not MTD ..,

please do let me know where iam wroung

Regards,

Kiruthiga

Re: MTD not working

Thu, 27 Sep 2012 07:27:10 GMT

Hi,

May I know the value of $(EXP).

Regards,

Janzen

Re: MTD not working

Thu, 27 Sep 2012 07:31:40 GMT

HJanzen,

Thanks,

$(EXP) = if(val=5,val2,val3)---> I made that as a variable,but there is not problem in this,because,

only if I calculate YTD its working fine

Re: MTD not working

Thu, 27 Sep 2012 07:32:49 GMT

HJanzen,

Thanks,

$(EXP) = if(val=5,val2,val3)---> I made that as a variable,but there is not problem in this,because,

only if I calculate YTD its working fine

Re: MTD not working

Thu, 27 Sep 2012 07:40:02 GMT

Hi,

Can you give the expression you used for MTD? It should work using this expression.

=if(Category='receivable',sum({<YEAR={$(=MAX(YEAR))},MONTH={'<=$(=MAX(MONTH))'}>} $(EXP)))

Regards,

Janzen

Re: MTD not working

Thu, 27 Sep 2012 08:14:13 GMT

Hi,

I used the same expression

as yours which is in the starting of my post..,

is there anything do with the month alone say 1-12 after that 13,14,15..for the next year??

any idea?

because I am not getting only MTD..,

Re: MTD not working

Thu, 27 Sep 2012 08:47:17 GMT

Hi,

There is a difference between this expression compared to your earlier post. This is for MTD computation.

=if(Category='receivable',

sum({<YEAR={$(=MAX(YEAR))},

MONTH={'<=$(=MAX(MONTH))'}>}

$(EXP)))

while this one is for YTD

=if(Category='receivable',

sum({<YEAR={'<=$(=MAX(YEAR))'},

MONTH={'<=$(=MAX(MONTH))'}>}

$(EXP)))

Hope this will help

Regards,

Janzen

Re: MTD not working

Thu, 27 Sep 2012 11:50:09 GMT

Hi Janzen,

sorry in delay replying ,I tried with MTD but it is showing the exact value

means if i select feb 2011 it is giving only feb 2011 data,

but i need from jan 2010 to feb 2011 value for selecting feb 2011(Data is available from jan 2010 only)

Re: MTD not working

Fri, 28 Sep 2012 02:11:56 GMT

Hi,

Sorry I was confused with your question. What do you really need? Based on what I know MTD means from the first day of a selected month and last day of a particular month. Example if you choose Feb 2011, MTD means from Feb 1, 2011 up to Feb 28, 2011. YTD means from the first day of a selected year up to the selected day and month. Example if you choose Feb 2011, YTD means from Jan 1, 2011 up to Feb 28, 2011. May I ask for your definition of MTD and YTD?

Regards,

Janzen

Re: MTD not working

Fri, 28 Sep 2012 06:03:07 GMT

Hi Janzen,

Thanks

Sorry for confusing you,

my requirement is

if you choos Feb ,2011

if the data in database is from say jan 2010

then it need to display data from Jan 2010 to feb ,2011

my problem is if I select only year say 2011,it is givin the sum of data(2010 + 2011 for 2011) correctly,

but if I choose the month after year selection say febafter selecting year -2011,the data is comming wroung

But YTD is enough for my scenario I think so and my expression below

if(Category='receivable',sum({$<Year={"<=$(=max(Year))"}>}$(exp))

Its very urgent ,i need to completethis by today..,there is some small mistake in my expression which iam not able to deduct..,

Hope to hear from you

Kiruthiga

Re: MTD not working

Fri, 28 Sep 2012 07:16:50 GMT

Hi,

What is your common field between tables? Is it by date or month and year only? Sometimes the expression varies. Please try this expression.

=if(Category='receivable',sum({$<Year = {"<=$(=max(Year))"},Month = {"<=$(=max(Month))"},Month =,Year = >} $(EXP)))

Hope to hear from you,

Regards,

Janzen

Re: MTD not working

Fri, 28 Sep 2012 07:32:20 GMT

You can use below script for ytd

Sum({<Year={$(=Max(Year))},AMonth={$(=Max({<Year={$(=Max(Year))}>}AMonth))},ADate={"<=$(Date(Max(ADate)))"}>} Qty)

It will help you bit.

Re: MTD not working

Fri, 28 Sep 2012 08:03:51 GMT

Janzen,

its not woking,Actually if i try with my expression just in my above post,when i replace the year field to month ,

its not working,

means if i select jan alone with out selecting any year,its displaying nothing..,

but it need to give jan month data field right?

Re: MTD not working

Fri, 28 Sep 2012 08:12:52 GMT

kOUSHIK,

Its nor working...,iam going mad on this issue 😞

Re: MTD not working

Fri, 28 Sep 2012 08:22:17 GMT

Hi,

You dont need to replace the year field to month. For MTD, my last expression should work. Based on your example, you should also choose a year after choosing january as a month to determine the end date. So this means from January 2010 upto January of the selected year.

Regards,

Janzen

Re: MTD not working

Fri, 28 Sep 2012 09:10:57 GMT

Janzen,

Thanks..,

yup its working with the concept of January 2010 uptoJan selected year

sorry confused...a little,

if i select march 2012,

can i get jan 2010 to mar 2012 ?(sum of values from jan 2000 to mar 2012 as an expression value,means a total of last selected month and year from the starting year and month irrespective of month alone)

really thanks for your valuable timingssssssss

Regards,

-Keeruthii

Re: MTD not working

Fri, 28 Sep 2012 09:21:01 GMT

Hi,

Yes as long as you have your data up to March 2012. If it gives you the right answer, kindly tag it as the correct answer for future references.

Regards,

Janzen

Re: MTD not working

Fri, 28 Sep 2012 09:44:59 GMT

Sure Janzen,I did ,

Thanks 🙂

Re: MTD not working

Mon, 01 Oct 2012 04:58:19 GMT

Try below code.

sum({<Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'},Day={'<=$(vMaxDay)'},Date=,MonthYear=>} Amount)

Variables:-

vMaxYear=Max(Year)

vMaxMonth=Date(max(Date),'MMM')

vMaxDay=day(max(Date))

Don't worry.I will help you to become MAD..

Just Kidding.

Try it.

Re: MTD not working

Mon, 01 Oct 2012 06:17:24 GMT

=Sum({$<Date = {">=$(vMinnDate)<=$(vMaxDate)"}>} QTY)

In ur calendar use below code..

Caltemp:

LOAD
num(min(%TranDate)) as MinnDate

resident TRANSACTION_TABLE;

LET vMinnDate = peek('MinnDate',0,'Caltemp');

Declare a variable...

vMaxDate = date(MonthEnd(max(Date)),'DD MMM YYYY')

It will be helpful.