Last Week vs. Last Week Previous Year ect...

[#1]

I am looking for some instructions on how to get a view within Qlikview that looks like the following:

I have been using a competitor's visualization tool and am wondering if Qlikview can do what I need in a more fashionable way.

My data is broken down by day over the past 3 years and can be in either format:

Date, Revenue, Orders, Visits ...

Jan 1, 54434,    56,        783432

OR

Date,  Metric,  Value

Jan 1, Revenue, 54434

Jan 1, Orders,   56

Jan 1, Visits,    783432

We operate on a Monday to Sunday basis so if today is Jan 4, 2012 "Last week" would mean December 26, 2011 through January 1, 2012.

Last week Previous Year would be same week as last week one year ago.

Week Previous would be December 19, 2011 through December 25, 2011.

% Diff mean "(last week - last week prev year) / last week"

I am somewhat new to Qlikview and am unfamiliar with the scripting process or the funcions within the expression box so any and all help is much appreciated.

Please let me know what you can come up with because I am kind of at a loss right now.

I am using Personal Edition so I can't open any other documents either, so screenshot or something of the like would be best.

Thank you tons in advance

Jared

[pover]

Jared,

You would define the Metric field as a dimension for your report.  The order of the metrics would either be load order or manually define with an if().  If you are using Tableau this is a little different, but there are specific posts on how to do that in the community.

The columns should be done using set analysis and the week function that you can define as starting any day that you like.  You can search for set analysis in the help section and in the community.  The week function explanation in the help section should be enough.  You can also refer to other columns either by the column(x) function or using the column label.  This will help you calculate the differences.

If the conditional text color is simple (eg. negative is red and postive is green), you can define it in the Visual Cues tab in the properties window of straight or pivot table.  If you want to reproduce something like in Tableau where it is a range of colors you could use the colorMix() function defined in the Text Color property of the expression that you can modify by clicking on the plus next to the expression.

I've tried to break down the process without getting too specific because there are other posts in the community that attack each point more specifically.  If you get caught up with any specific issue and can't find the solution, please tell us.

Karl

[#3]

Thank you for your reply. I have gone through the set metrics section of the help and the reference guide and the date time functions of both as well but still can't seem to get my mind around it.

I have been using Tableau and use the formulas:

IF DATEPART('week',[Date Starting Monday])=DATEPART('week',DATEADD('week',-1,[Today]))

  AND DATEPART('year',[Date Starting Monday])=DATEPART('year',DATEADD('week',-1,[Today]))

THEN [Value]  END

or for a single metric

IF DATEPART('week',[Date Starting Monday])=DATEPART('week',DATEADD('week',-1,[Today]))

  AND DATEPART('year',[Date Starting Monday])=DATEPART('year',DATEADD('week',-1,[Today]))

THEN [Cost] End

This gives me data for last week only, and simple changes to that formula give the QTD or Last Week Prev Year values. However, I can't get a starting point within QlikView.

If you could help me with an example of a formula that would work in QlikView I would be very appreciative and hopefully I can go from there.

[pover]

Jared,

Here's an example of how to filter by week:

sum({$<OrderDate={">=$(=weekstart(max(OrderDate),-1))<=$(=weekend(max(OrderDate),-1))"}>} Sales)

Instead of max(OrderDate) you can use today() and the -1 means last week.  The previous week is -2 and I don't know how you calculate the last week last year, but it might be -53.  There is also a quarterstart() and a quarterend() function.  The functions return complete dates so it is not necessary to also to compare the year.

Karl

[#5]

Hi Karl,

I have a similar issue. The set analysis i'm using needs to return me the the previous week

=sum({$<Book={"EM MATCH"},CalendarDate = {'$(= weekstart(CalendarDate),-1)'}>}PRICE)

This doesn't seem to work though. What am I doing wrong?