Time Period using Master calender

[#1]

Hi community,

I have table like

Invoices:

Invoice num, Amount, approval date

333, 500, 1/7/2014

124,  600, 1/6/2014

125, 700, 1/7/2014

126, 800, 1/8/2014

123, 5004, 1/9/2014

124,  6200, 1/10/2014

125, 7200, 1/11/2014

126, 2800, 1/12/2014

123, 500, 1/1/2015

124,  600, 1/2/2015

125, 700, 1/3/2015

126, 800, 1/4/2015

123, 500, 1/5/2015

124,  600, 1/6/2015

125, 700, 1/7/2015

like this i have data with three columns,

no my requirement is I have to get the months in filter  like "last 12 months", "last 6 months", "last 3 months" and

for the current FY i need "Current Year" and for previous 12 months i need to show  as "Previous Year"instead of "FY Q1", "FY Q2", "FY Q3" , "FY Q4"

when i reload with new month's data selection should also change to from current month to as per selection eg: when i select last 3 months i should get the data of august, july, june.

Overall i need to create column in script with

TimePeriod

Current year,

last 12 months,

last 6 months,

last 3 months,

Previous year

thanks

Reddy

[marcus_sommer]

You couldn't create this flagging only in the script and could choose them within a single listbox without to adjust expressions in the gui. Your time-periods eliminate eachother within one field.

The nearest way to reach your requirements is to create for each time-period an own flag, create a small inline-table with your time-periods names and using from a variable which returned the flag of the choosen period:

variable: vFlag

= pick(match(getfieldselections([Time Periods], 'Last 12 Months', ...), $(vFlagLast12M), ....)

expression:

= sum(value * $(vFlag))

or vFlag would be used within a selection-trigger to select the flag-field:

action-field:

$(vFlag)

action-value:

= 1

- Marcus

[#3]

Hi marcus,

My requirement is Qlik sense based.

i have to do it in back end only... is there  any other way.

Thank you.

[ogster1974]

‌mMarcus idea will work in SEnse if ŷou use the qsvariable extension to let the user set your date period selection.  It even have a drop down control for your different selections.

REgards

Andy

[rwunderlich]

This example shows how to create temporal date groups in script.

Qlikview Cookbook: Date Grouping http://qlikviewcookbook.com/recipes/download-info/date-grouping/

I know you are asking about Sense. so here it the script which will work in Sense as well.

// Assign current date to a variable. Makes INLINE below a little neater and ensures we don't cross midnight boundary.

LET vToday=today(1);

Ranges:

// Use the "evaluate()" function to execute the input function text and convert it to actual date values.

LOAD Range, date(evaluate(RangeStart)) as RangeStart, date(evaluate(RangeEnd)) as RangeEnd

;

/* The INLINE data defines the Range Name, Starting date of the Range, and Ending date of the Range.

The Start/End values are defined using standard QV date functions.

Semicolon is used for a field delimeter because some of the functions contain commas.

*/

LOAD * INLINE [

Range; RangeStart; RangeEnd

Today; vToday; DayEnd(vToday)

Yesterday; vToday-1; DayEnd(vToday-1)

This Week; WeekStart(vToday); WeekEnd(vToday)

Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1)

Last 2 Weeks; WeekStart(today(1),-1); WeekEnd(today(1))

This Month; MonthStart(vToday); MonthEnd(vToday)

Last Month; MonthStart(vToday,-1); MonthEnd(vToday,-1)

Last Two Months; MonthStart(vToday,-1); MonthEnd(vToday)

This Year; YearStart(vToday); YearEnd(vToday)

Last Year; YearStart(vToday,-1); YearEnd(vToday,-1)

] (delimiter is ';')

;

/*

Use IntervalMatch to link the Date field into multiple Range fields.

Doing a JOIN avoids creating a messy synthetic key.

*/

JOIN (Ranges) IntervalMatch (Date) LOAD RangeStart, RangeEnd RESIDENT Ranges;

// Because we did a JOIN, we may drop the the Start/End fields.

DROP FIELDS RangeStart, RangeEnd;

[marcus_sommer]

I'm not sure if nowadays variables could be used within sense. But generally it would work without them - it's only a bit simplifying and neater to use variables to keep the expression clean. Everything else are normal expressions and should work in sense, too.

But the suggestion from Rob looked very interesting - I would try it.

- Marcus