Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Time Period using Master calender

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

14 Replies
harsh44_bhatia
Creator
Creator

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:

Capture.PNG

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

Not applicable
Author

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.

time.png

harsh44_bhatia
Creator
Creator

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.

Not applicable
Author

Yes Harshith,

some more clarification needed and  am working on qlik sense.

result should be as shown in screenshot above added.

thanks

harsh44_bhatia
Creator
Creator

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

marcus_sommer

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

ogster1974
Partner - Master II
Partner - Master II

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.

Not applicable
Author

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

Not applicable
Author

hi harshith,

everything needs to be done in script only

i should not do anything in front end

thanks