Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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
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
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
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.