Charting current 4wks versus last 4wks

[Anonymous]

I have a dashboard which compares some KPI on current 4wks versus the last 4wks.  Please see attached .  i also have a straight line chart for trending the dashboard.  I would want the straight line graph to show a comparison of the current 4wks versus the last 4wks on the same chart and differentiate the two trend lines. Is this possible?  I am using weekYear and an expression for the charting which is as follows:

Count([Repeat or Original])

Regards.

[Anonymous]

I would add a weekYearSeq to your calendar:

calTemp1:

Load distinct

Year,

Week

Resident Calendar

Order by Year, Week;

Left Join (Calendar)

Load

Year,

Week,

RowNo() as weekYearSeq

Resident calTemp1

Order by Year, Week;

Drop Table calTemp1;

2 Expressions:

Recent 4:

count({$<weekYearSeq={">=$(=Max(weekYearSeq)-3<=$(=Max(weekYearSeq)))"},Year,Week,weekYear>}[Repeat or Original])

Previous 4:

count({$<weekYearSeq={">=$(=Max(weekYearSeq)-7<=$(=Max(weekYearSeq)-4))"},Year,Week,weekYear>}[Repeat or Original])

You'll use sequence numbers like this a lot. It is worth adding them to your calendar.

[Anonymous]

Many thanks Steve.  I have managed to trend the current 4 weeks vs the last 4 wks using the following expression(see attached):

Count({$<$(vSetPreviousNMonth(0))>} [Repeat or Original]) + Count({$<$(vSetPreviousNMonth(1))>} [Repeat or Original])

My calendar uses vSetPreviousNMonth()

However i still have not managed to change the line style in order to differentiate the two.  If i have field names I could have done this through the expression properties through the following:

=if(IsForecast = -1, '<S1>', '<S3>') where IsForecast is a field name.

Regards

Chris

[Anonymous]

Hi Steve

Further to my last correspondence, i am adding these to my calendar and variables.  What are the corresponding expressions for:

1. Recent 8 weeks
2. Previous 8 weeks
3. Recent 12 weeks
4. Previous 12 weeks

I would want generic expressions which do not have [Recent or Original] so that they can be used in future for other projects.

Regards.

[Anonymous]

It is getting hard for me to think of this in a purely theoretical sense. Any chance you could post a sample document?

When you say you've added "these" to your calendar, were you referring to suggestion for adding the sequence numbers? If so, your task is fairly straightforward. Otherwise I'm not entirely sure what you're looking for.

[Anonymous]

Dear Steve

Yes i have update my calendar to include your suggestions on sequence number, see below:

QuartersMap:
MAPPING LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AUTOGENERATE (12);

Temp:
LOAD
min(Date(Floor([DateDispensed]))) as minDate,
max(Date(Floor([DateDispensed]))) as maxDate
Resident [Main Data];

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD DISTINCT
$(varMinDate) + IterNo()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load *,
AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID],
AutoNumber(Period, 'PeriodID') as [PeriodID]
;
LOAD DISTINCT
TempDate as [DateDispensed],
Year(TempDate) * 100 + Month(TempDate) as [Period],
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
YearToDate(TempDate)*-1 as CurYTDFlag,
YearToDate(TempDate, -1)*-1 as LastYTDFlag,
InYear(TempDate, MonthStart($(varMaxDate)),-1) as RC12,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(Weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate asc;
DROP Table TempCalendar;

Filtered:
LOAD
Year as Last5Year
Resident [MasterCalendar] Where Year >= Year(Today()) - 4 and Year <= Year(Today());

calTemp1:
LOAD Distinct
Year,
Week
Resident MasterCalendar
Order by Year, Week;

Left Join (MasterCalendar)
LOAD
Year,
Week,
RowNo() as weekYearSeq
Resident calTemp1
Order By Year, Week;
DROP Table calTemp1;

////--- Remove the temporary variables
LET varMinDate = Null();
LET varMaxDate = Null();
 

What i was asking is what are the equivalent expressions for:

1. 8 weeks (Recent and Previous)

2. 12 weeks (Recent and Previous)

I am very new to ClikView

I added the two expressions for 4 weeks into my set variables (vRecentFourWeeks and vSetPreviousFourWeeks using the two examples you gave) and tried to use them but was getting errors.  For example, the below expression gave me errors.  Where am i  going wrong?

Count({$<$(vRecenFourWeeks)>})

Regards

Chris

[Anonymous]

Hi Chris,

Thanks for the additional clarification.

I've had issues, myself using variables in this context that have search terms in the set analysis expression. Something about the double quotes seem to throw it off.

Have you tried using the code I supplied directly as an expression in your chart/report? I know that's not the approach your looking for and perhaps someone else following this thread can show us a way to do this, but I've yet to find it. Set variables are nice to use, but do have some limitations (double quotes).

[Anonymous]

Hi Steve

Thank you very much for the prompt response, most appreciated. I have not tried using the supplied expressions directly.  I have  had issue with that approach and hence the move to using variables.

Regards.

Chris

[Anonymous]

Hi Steve, i have used the expressions directly as advised.  However, the two graphs yield the same results, one superimposed on the other?

What could could i be getting wrong?

Regards