Howto make a variable to calculate quarters regarding year changes?

[benjamins]

So I have the following variable definition:

var1:     =Year(today())&ceil(month(today())/3)

Using this variable I get '20143', which is what I expect.

When I subtract something from this number like 1, to get the last quarter I now currently get '20142', which is fine now, but will fail from January to March each year. For example in January 2015 I'd want to get '20144' as a result, but I will get '20150'.

What would be the correct way to go about that? I also want to be able to subtract any other integer to get the appropriate quarter.

[#2]

Hi,

Try to use the below formula.

(Year(Temp_Date)-1)*12+Num(Month(Temp_Date))

For example, this formula will assign the value 24120 to Dec 2009 and 24121 to Jan 2010 and so on.

[marcus_sommer]

Within an expression like yours you needed some if-loops to solve this challange but this makes the calculations complex and slow. Better is often to generate and use a QuarterNum within the script from master-calendar - have a look on my MonthCounter, you could simply adapt the logic for quarters:

if($(i) = 0, 1, if(num(month($(vStart) + $(i))) = peek('Monat', $(i) - 1, 'Kalender_0'),
     peek('MonthCounter', $(i) - 1, 'Kalender_0'),

     peek('MonthCounter', $(i) - 1, 'Kalender_0') + 1)) as MonthCounter,

$(vStart) is the earliest date in my calendar and $(i) is a loop-counter

- Marcus

[benjamins]

Hmm, I'm sorry, I'm not firm enough with qlikview scripting to get what you are doing here. I tried implementing what yout proposed, but I can't seem to make it work. Please see the attachment.

[marcus_sommer]

Have a look on the attachment and http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

- Marcus

[benjamins]

Yes, this works as expected - thank you so much. Just one more (probably easy) thing: How do I set the variable from my original post to the quarternumber that represents today?

[marcus_sommer · Accepted solution]

This could be solved with lookup:

let vCurrentQuarterNumber = lookup( 'QuarterCounter', 'DateId', date(today(), 'DD/MM/YYYY'), 't1' );

- Marcus

[benjamins]

Works!

Again: Thank you very much.

[Anonymous]

Hi Marcus,

Can you help me with my problem:

Data for date is month end only = 30 June, 31 Jul, 31 Aug, 30 Sep, Measure - 100, 50, 200,400

Need to create a parameter/variable for period = Monthly or Quarterly.

Date range filter as calendar. User can select start date and end date and sheet data will filter accordingly.

When user selects Period = "Monthly" all month end dates should appear in  bar (keep any measure).

When user selects Period = "Quarterly" only latest month for that particular quarter should appear based on date range filter by user.

for e.g

if user doesn't filter out any data as above

o/p

June - 100

Sep - 400

if user filter Sep from data then

June - 100

Aug - 200

[marcus_sommer]

At first I suggest to consider if a separate selection of monthly or quarterly is adding any more value - why should the user not just select those month (and/or maybe quarters) which he/she wants to see - nothing could be easier and everything else is adding an extra layer of complexity (not only by creating the app/objects else also in the usability).

Beside this you should use a master-calendar. Even if you have not a full year-calendar else only the monthends or monthstarts it would be very useful to be able of using all the date-functionalities in fields and expressions.

If you want to continue with your approach you will either need some kind of branching in the different views or to integrate some logic within the datamodel. Branching means to use an if-loop to query and react on user-selection and a solution within the datamodel could be to create a flag (in the master-calendar) with maybe 1 for monthly and 2 for quarterly and associating them with your selection-field.

- Marcus