Editing expression to get current QTD and last QTD

[Anonymous]

Hi,

New to Qlik and don't want to mess too many things up in an existing app. I have an expression that is currently getting current YTD data. There is a column in the data set called Quarter_new with data populating Q1, Q2, Q3, or Q4. There is also a column Year_new with the correct year. Is there a way to just edit the expression syntax to get QTD data and last QTD?

Current Expression:

if([_dimension2]='Cycle Time -Average cycle time – Print',

round(avg({<Date_new={'>=$(vYTDCYStart)<$(vYTDCYEnd)'},Year_new=,Month_new=,Quarter_new=,Medium={'Print/Physical'},Product=,Franchise=>}[FF - Time to AFU (Days)])),

if([_dimension2]='Cycle Time -Average cycle time – Digital'

Appreciate anyone's help on this.

Thanks,

Ale

[Anonymous]

I wouldn't recommend pre-aggregating your data.  Qlik works best when you leave your data at the lowest level and do the aggregations on-the-fly.  One's solution is very dependent on their data model.  In these cases it is advisable to use a date dimension.  As you've already found, set expressions can get messy quickly.  A better model can help keep your expressions in check.

I'm not saying that this will solve your problem, but you should read this post.  It might give you some ideas for how best to structure your data.  here.

RTP stands for Relative Time Periods.  This script leverages your existing month dimension to add the following relative time elements to your app:

• To-Date ranges such as YTD and QTD
• Relative time periods such as "Previous Year", "Previous Month", or “same period last year”
• Rolling periods which are used for moving averages. (Ex. "Rolling 3 month average")
• Year-over-Year and MoM growth metrics

Not only that, these RTPs will work for any anchor month! (not just the current period)

This approach greatly simplifies your measures by only requiring two set modifiers.