Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

kumarreddy257
Not applicable

MTD for select year

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

5 Replies
Not applicable

Re: MTD for select year

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...

Not applicable

Re: MTD for select year

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

kumarreddy257
Not applicable

Re: MTD for select year

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

kumarreddy257
Not applicable

Re: MTD for select year

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

Not applicable

Re: MTD for select year

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.