- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Using dynamic month in set analysis based on month-year selection
Hi,
I've an analysis based on the actual month that compares values from this month to the previous one.
count( {$<Month={$(=Month(now()))},Year=>} Activation_id )
/count({$<Month={$(=Month(AddMonths(now(),-1)))},Year={$(=Year(AddMonths(now(),-1)))}>}Activation_id)-1
It works ok but, what I'd like to do now, is to change it so that if I select a month-year from a left side calendar, the analysis will consider the selected month against the month before.
I've a Calendar loaded in this way:
Calendar:
LOAD
DateId,
date(DateId) as DateEvent,
day(DateId) as Day,
week(DateId) as Week,
month(DateId) as Month,
dual(month(DateId) & '-' & year(DateId) , year(DateId) & num(month(DateId), '00')) as MonthYear,
year(DateId) as Year,
weekday(DateId) as Weekday,
(year(DateId)-1)*12 + Num(Month(DateId)) as MonthId,
'Q' & ceil(month(DateId)/3) as Quarter
RESIDENT Date_src;
What i tried to do is to change {$<Month={$(=Month(AddMonths(now(),-1)))},Year={$(=Year(AddMonths(now(),-1)))}>} using MonthId but it did not work when I selected a specific month.
do you have any idea on how to do it?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you use Qlikview Components to create a calendar from your DateId field you'll get a lot of nice calendar fields and set analysis modifiers with very little effort. If you don't want that you can try:
count( {$<MonthYear=,Month={$(=Month(max(DateId)))},Year=>} Activation_id )
/count({$<MonthYear=,Month={$(=Month(AddMonths(max(DateId),-1)))},Year={$(=Year(AddMonths(max(DateId),-1)))}>}Activation_id)-1
talk is cheap, supply exceeds demand
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you use Qlikview Components to create a calendar from your DateId field you'll get a lot of nice calendar fields and set analysis modifiers with very little effort. If you don't want that you can try:
count( {$<MonthYear=,Month={$(=Month(max(DateId)))},Year=>} Activation_id )
/count({$<MonthYear=,Month={$(=Month(AddMonths(max(DateId),-1)))},Year={$(=Year(AddMonths(max(DateId),-1)))}>}Activation_id)-1
talk is cheap, supply exceeds demand
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thanks for the link: I will try to implement the calenda you suggested but it might take me a while.
In the meantime I tried your suggestion but it does not work: I split the formula and
count( {$<MonthYear=,Month={$(=Month(max(DateId)))},Year=>} Activation_id ) --> works ok returning values
count({$<MonthYear=,Month={$(=Month(AddMonths(max(DateId),-1)))},Year= {$(=Year(AddMonths(max(DateId),-1)))}>}Activation_id) --> does not return any value
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Gysbert,
I realized the error occurred when i selected only the month without the year: I will work on it in order to take, by default, the current year if a user select only a month and not a month&year
thanks,
g