Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

benjamins
Contributor

Howto make a variable to calculate quarters regarding year changes?

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.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Re: Re: Re: Howto make a variable to calculate quarters regarding year changes?

This could be solved with lookup:

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

- Marcus

View solution in original post

10 Replies
Not applicable

Re: Howto make a variable to calculate quarters regarding year changes?

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.

MVP & Luminary
MVP & Luminary

Re: Howto make a variable to calculate quarters regarding year changes?

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
Contributor

Re: Re: Howto make a variable to calculate quarters regarding year changes?

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.

MVP & Luminary
MVP & Luminary

Re: Re: Re: Howto make a variable to calculate quarters regarding year changes?

benjamins
Contributor

Re: Re: Re: Howto make a variable to calculate quarters regarding year changes?

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?

MVP & Luminary
MVP & Luminary

Re: Re: Re: Re: Howto make a variable to calculate quarters regarding year changes?

This could be solved with lookup:

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

- Marcus

View solution in original post

benjamins
Contributor

Re: Re: Re: Howto make a variable to calculate quarters regarding year changes?

Works!

Again: Thank you very much.

luvneries
New Contributor

Re: Howto make a variable to calculate quarters regarding year changes?

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

MVP & Luminary
MVP & Luminary

Re: Howto make a variable to calculate quarters regarding year changes?

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