topic Re: MTD in QlikView

MTD

NavinReddy — Fri, 14 Feb 2014 06:46:44 GMT

Dear All,

LOAD * INLINE [

Date, Salary

01/01/2014, 100

03/01/2014, 200

06/01/2014, 300

07/01/2014, 400

10/02/2014, 500

11/02/2014, 600

12/02/2014, 700

13/01/2014, 800

];

I have simple data, In this data how can i fetch Current month Salaries how can i fetch

=sum({<Date={'>=$(=MonthStart)<=$(=MonthEnd)'}>}Salary)

i Tried this expression but it is not working

Thanks In Advance

Niranjan

Re: MTD

jonathandienst — Fri, 14 Feb 2014 06:50:43 GMT

To base the month on today's date:

=sum({<Date = {'>=$(=MonthStart(Today())) <=$(=MonthEnd(Today())'}>} Salary)

HTH

Jonathan

Re: MTD

sundarakumar — Fri, 14 Feb 2014 06:51:01 GMT

U want to restrict in edit script itself or u want them to hide them while showing charts?

-Sundar

Re: MTD

sundarakumar — Fri, 14 Feb 2014 06:59:29 GMT

Hi Niru,

If you want to restrict the data in edit script then you can use PFA(inline)

load

* inline
[
date,salary
1/1/2012,100
2/2/2012,200
3/3/2012,300
1/1/2014,400
2/2/2014,500
]
where date>monthstart(today());

If you want to restrict in the chart you can do it as PFA(inline_chart)
Hope this helps

-Sundar

Re: MTD

jagan — Fri, 14 Feb 2014 07:01:06 GMT

Hi,

Try like this

=sum({<Date={">=$(=MonthStart(Today(), 'DD/MM/YYYY'))<=$(=Date(Today(), 'DD/MM/YYYY'))"}>}Salary)

Regards,

Jagan.

Re: MTD

jagan — Fri, 14 Feb 2014 07:15:07 GMT

Hi,

Can you close this post also.

Regards,

Jagan.

Re: MTD

stevedark — Fri, 14 Feb 2014 07:54:54 GMT

Your best bet for selecting a month is to split the month out in the load script. This can then be selected in a list box or used as a dimension etc.. The code for this is:

LOAD

Date,

Salary,

Month(Date) as Month,

Date(MonthStart(Date), 'MMM-YY') as [Month Year],

Year(Date) as Year

INLINE [

Date, Salary

01/01/2014, 100

03/01/2014, 200

06/01/2014, 300

07/01/2014, 400

10/02/2014, 500

11/02/2014, 600

12/02/2014, 700

13/01/2014, 800

];

You could then create a variable, called vCurrentMonth with the following code:

=Date(today(), 'MMM-YY')

And your expression would be:

=sum({<[Month Year]={'$(vCurrentMonth)'}>}Salary)

There are a lot more date functions you can use to split your date out into various permutations (Day, WeekDay, Week etc.) you will find information on this in the help or on line.

I've a couple of examples on Qlik Community of period to date and prior period that you may want to take a look at:

QlikView App: Simple Year To Date Example (Set Analysis)

QlikView App: Set Analysis - Prior Period Comparison

Hope that helps

Steve