Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

alekong1
New Contributor

Editing expression to grab QTD data

Hi all,

Newbie here trying to edit an existing expression below from a table. The current expression is grabbing data YTD. The new ask is for the data to be grabbed QTD. How would I adjust the expression to properly grab QTD data?

if([_dimension2]='Volume -Percentage of expedited reviews',

Num(sum({<Date_new={'>=$(vYTDCYStart)<$(vYTDCYEnd)'},Year_new=,Month_new=,Quarter_new=,Product=,Franchise=>}[Expedited Review Jobs])
/
count({<Date_new={'>=$(vYTDCYStart)<$(vYTDCYEnd)'},Year_new=,Month_new=,Quarter_new=,Product=,Franchise=>}new_job_count),'##.0%'),
if([_dimension2]='Quality -Percentage of jobs failing QA',
num((sum({<Date_new={'>=$(vYTDCYStart)<$(vYTDCYEnd)'},Year_new=,Month_new=,Quarter_new=,Product=,Franchise=>}[QA Failed])
/
(sum({<Date_new={'>=$(vYTDCYStart)<$(vYTDCYEnd)'},Year_new=,Month_new=,Quarter_new=,Product=,Franchise=>}[QA Failed])
+sum({<Date_new={'>=$(vYTDCYStart)<$(vYTDCYEnd)'},Year_new=,Month_new=,Quarter_new=,Product=,Franchise=,[QA Review Cycle Count]={'1'}>}[QA Review Cycle Count]))
),'##.0%'),
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',
round(avg({<Date_new={'>=$(vYTDCYStart)<$(vYTDCYEnd)'},Year_new=,Month_new=,Quarter_new=,Medium={'Digital'},Product=,Franchise=>}[FF - Time to AFU (Days)]))
))))

Thanks,

Ale

Tags (2)
4 Replies
JustinDallas
Valued Contributor II

Re: Editing expression to grab QTD data

Well, you would probably have to make two variables called "vQTDStart" and "vQTDEnd" and replace their equivalent values in the statement.


Quarter start and end might be calculated by using the following


LET vQTDStart = quarterstart( Today() )

;

LET vQTDEnd = quarterend( Today() )

;

You also might want to read up on the MasterCalendar concept.  It isn't always useful, but when it is, it saves a lot of time and space and makes Set Analyses more readable.

alekong1
New Contributor

Re: Editing expression to grab QTD data

Hi Justin,

Thank you for the reply. Is there a way to just update the existing expression. I'm new to Qlik and would like to figure out the easiest solution to update the table.

Thanks,

Ale

JustinDallas
Valued Contributor II

Re: Editing expression to grab QTD data

Yes, just create those variables in the script, and then do a CTRL-R and replace the YTD variable with the QTD variable.

alekong1
New Contributor

Re: Editing expression to grab QTD data

Hi Justin,

So I would prefer not to mess with the script. In terms of the data, I do have quarter data as (Q1, etc) and I have month (Jan, Feb, etc), year (YYYY).

Is there anyway to just adjust the expression to get current QTD and last QTD?


Thanks,

Ale

Community Browser