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
Hi,
You will have to use the concept of Dollar Expansion in dimension tab along with some macros coding.
For this you will have to create data islands as follows:
Code this as an an expression in list box: Range.$(Value_form_Type). this way the list box will be dynamic and will show values for Range.Year, Range.Month, Range.Week in a single list box as per the value selected on Type
So when Someone selects Year the Range.$(Value_form_Type) list box is showing the value for Year.
Now for each of the values write a Macro so for Range.Year when selected value is Current the macro will select the Current Year from the master calender. and so on you may write Macros for the others too
Hi Harshith,
thanks for the reply.
I want everything need to be done in script only.
In dashboard when i use a filter pane i should get options as current year, last 12 months, last 6 months , last 3months, previous year.
Hi,
Yes you have to do this in script only.
You have to create the data island in script, but to see them on the UI you have to create the list boxes. Again when someone select the values in these list boxes you have to write macros for that, which will make your automatic selections on Master calendar.
Let me know if you need more clarification on this.
Yes Harshith,
some more clarification needed and am working on qlik sense.
result should be as shown in screenshot above added.
thanks
Hi Prashanth,
what you request we did exactly in QlikView so i suppose this would work in Sense too.
for QlikView Scripting:
Create these data island:
DateType:
LOAD * INLINE [
Type
Year
Month
Week
Custom
];
YearType:
LOAD * INLINE [
Range.Year
Current
Previous
];
MonthType:
LOAD * INLINE [
Range.Month
3 Months
6 Months
12 Months
];
WeekType:
LOAD * INLINE [
Range.Week
1 Week
2 Week
4 Week
];
Now create Two List boxes, One with Type and another with the expression as follows: =$(='Range.'&vType)
where vType is a variable defined in UI as
vType==GetFieldSelections(Type)
as you make selections list box Type your expression list box will change dynamically and list values as per selection ie. for year, months 7 weeks
the next part is Macros : which you may write in VB for what ever value is selected in Range.(Type) your macro will run and select the date range from Master calender accordingly
I suggest to create flags for this within the master-calendar: Calendar with period flags. More informations will you find here: How to use - Master-Calendar and Date-Values
- Marcus
Sense doesn't support Marcos so unforunately won't work.
IF you want to use variables in your code there is an extension you can use called qsvarible which has a drop down control you could use.
Hi marcus,
i need to create a dimension (eg: time period)
When i use it in filter pane : it should should me options like:
Time Period (values in the order below) |
Last 12 Months |
Last 6 Months |
Last 3 Months |
Current Fiscal YTD |
Previous Fiscal YTD |
Previous 12 Months |
FY16 |
FY15 |
FY14 |
FY13 |
if i select 12months , i should get the data from Aug15 to sep14
thanks
Prashanth
hi harshith,
everything needs to be done in script only
i should not do anything in front end
thanks