Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I need to make a comparison of months by day using a line chart. The user should be able to click on the yearmonth field to pick the months that they want to compare. (each year-month will be a different line)
The x-axis will contain day numbers i.e., 1 to 31.
The y-axis will contain sales (cumulative daily so on day 1 it will show amount of sales made on day 1 of each month selected, on day 2 it will show the sales for day 1 + sales for day 2 for each month selected, etc)
Thank you in advance for your advice.
PS
I do have a master calendar:
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),
Month($1) AS [Month] Tagged ('$month'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),
Date(Floor($1)) AS [Date] Tagged ('$date');
DERIVE FIELDS FROM FIELDS [Date], [pas.Date],[Loan Date], postingdate, loandate USING [autoCalendar] ;
My current attempts which are not working:
//= sum({< [Date.autoCalendar.Date]= {">=$(=MonthStart(Today()))<= $(=MonthEnd(Today()))"}>} [Unique Subs])
//Sum([Unique Subs])
//Sum({$<[Date.autoCalendar.Month] = {='Month(today())'}>}[Unique Subs])
try
= sum({< [Date.autoCalendar.month]= {$(month(today()))} >}[Unique Subs])
Would he not have to use something like MonthYear since we can have the same "Month" over many years?
I agree. that what I try to make his focus
May be something like -
rangesum(Sum(Sales) + Above(Sum(Sales))
You and Justin are making a lot of sense. I have a yearmonth dimension but I am not sure how to work with it. How would you do it?