topic Re: MTD for select year in QlikView

MTD for select year

Anonymous — Wed, 29 Apr 2015 06:17:19 GMT

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

Re: MTD for select year

Wed, 29 Apr 2015 06:31:21 GMT

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

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

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

Re: MTD for select year

Wed, 29 Apr 2015 06:46:20 GMT

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

Re: MTD for select year

Anonymous — Wed, 29 Apr 2015 09:45:07 GMT

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

Re: MTD for select year

Anonymous — Wed, 29 Apr 2015 09:56:50 GMT

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

Re: MTD for select year

Wed, 29 Apr 2015 10:40:08 GMT

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.