Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi marcus,
My requirement is Qlik sense based.
i have to do it in back end only... is there any other way.
Thank you.
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
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;
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