MTD for select year

[Anonymous]

Hi,

I am new to qlikview and I have a requirement to do MTD for particular year.

Here my expressions are

Col1=   Count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"},[Site Visit Post Page Event] = {0}>} [Visitor ID])

Col2=  Count({$<[Site Visit Hit Source]={"<>5,7,8,9"}, [Site Visit Post Page Event] = {0}>} [Visitor ID])

But  I want to roll up monthly like below

Pls help me how to resolve this issue.

Regards,

Kumar

[#2]

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

or

You could load your data from sql with month & ' ' & year as month...

[#3]

Dear,

For MTD  you will make in script year,month ,day of date.

year(date) as year,

month(date) as month,

day(date) as day, 

then   make a varible               =>   vCurrDate=today()-1

Now use this code for MTD(month till Date).

sum({<Year={$(=max(Year))}, month={$(=max(month))}, date={"<=$(vCurrDate)"}>}Col1)

Regards

Vimlesh

[Anonymous]

Hi Matthias,

As you suggested I tried and it is splitting by Month level, but rolling up is not happened properly. There is data mis match with day level if I calculate

Regards,

Kumar

[Anonymous]

Hi,

In your expression

sum({<Year={$(=max(Year))}, month={$(=max(month))}, date={"<=$(vCurrDate)"}>}Col1) col1 is coming like

Count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"},[Site Visit Post Page Event] = {0}>} [Visitor ID])

So finally it will be

sum({<Year={$(=max(Year))}, month={$(=max(month))}, date={"<=$(vCurrDate)"}>}Count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"},[Site Visit Post Page Event] = {0}>} [Visitor ID]))

The expression is validating perfectly. But the data is rolling up. It is giving blank.

Regards,

Kumar

[#6]

LOAD text(day(date)) as day, text(month(date)) & text(year(date)) as month_year, text(date) as date ;

    SQL SELECT date, xyz, xxx 

        FROM tabte_x;

Or you search a outer form to change the format.