Time Period Dimension for a Filter

[Anonymous]

What is the best way to build a dynamic timeframe?  what I am looking to do is  build pre set time periods based on a month/year coming from the data set.  Ideally, I would like to build a 3 month/6 month 12 month time period.  Rather than a user see in a filter drop down:

JAN 17

FEB 17

MAR 17

APR 17

MAY 17

JUN 17

JUL 17

AUG 17

SEP 17

OCT 17

NOV 17

DEC 17

Where they could go and pick as many months at a time, I would rather them see:

Rolling 3 Months

Rolling 6 Months

Rolling 12 Months

where:

Rolling 3 Months = DEC, NOV, OCT

Rolling 6 Months = DEC, NOV, OCT, SEP, AUG, JUL

Rolling 12 Months = Complete Data Set

What I would like is to be able to have a number of KPIs/charts on a sheet that if the filter box was "binned" accordingly would then respond to this bin.  I would like to avoid having to add any sort of reference to time periods to each KPI.

[pradosh_thakur]

you can still use the normal rolling calculations with

1:an inline load where Rolling 3 Months,Rolling 6 Months, Rolling 12 Months are the field.

2: use if(getfieldselection(), .....) in expressions.

this way you can use whatever you want and you wont be hardcoding Rolling 3 Months,Rolling 6 Months, Rolling 12 Months,

[Anonymous]

Can you explain step 2 a bit more.  I am new to Qlik Sense.  Not BI, but definitely new to Qlik Sense logic.

[pradosh_thakur]

sure

lets say  we have a "period" field where the value are 'Rolling 3 Months','Rolling 6 Months', 'Rolling 12 Months'

user can select any one of them or nothing at all

expression will be something like

pick(match(getfieldselections(period),'Rolling 3 Months','Rolling 6 Months', 'Rolling 12 Months')+1,expression for current month,expression for rolling 3 month ,expression for rolling 6months, expression for rolling 12 months)

use this expression where you want rolling month. Where ever you don't want to see rolling sum just use the normal expression for current period. i hope i am clear and this is what your requirement is.

ps: expression can contain typo error as typed directly in the reply. expression can be written in pick match as well as if-else

[Anonymous]

Hi Pradosh,

I have a similar kind of situation where my Reporting date field has data something like :-

30 Nov, 17

31 Oct, 17

30 Sep, 17

31 Aug, 17

My requirement is to extract Current month, Previous month and previous quarter from this field

For example

If Reporting date = June 30 2017
Current month-end = June 30 2017

Previous month-end = May 31 2017

Previous quarter-end = March 30 2017

Use cases:

• multiple options (current/previous month end or previous quarter-end) can be selected at the same time
• the 3 options should dynamically update the reporting date drop down list with the selected dates

Following is the example image on ultimately how it could look like.Any suggestions please??

[Anonymous]

Thank you, I will give this a try and let you know!

[pradosh_thakur]

Hi kapil

i understood your requirement still i would ask you to describe use case 2 a bit more. IF you could add a sample with just the required field if the data is sensitive and how you want to get it in the UI it would be a plus.

thanks

[Anonymous]

Thanks for coming back Pradosh.

What I meant in use case 2 was -Ability to multi select. For example if I click on Current Month and Previous Month, both values should be selected.

I have created a sample data for your reference.

Any suggestions on it plz.

[pradosh_thakur]

I have some license issue so currently not able to open you document. You have to describe it to me else someoneelse can help you out here.