# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

## Time in SET

Hi friends,

It's been to long since I used time in SET, try to avoid that, but now I have to build my time limits in a chart.

I need to see last period - always previous month, this year and for last year. Trying this but can't seem to get it to work:

Sum({\$<Year = {\$(=Year(Today()))}, Month = {\$(=Month(Today())-1)},Year=, Month=>} Amount))

and

Sum({\$<Year = {\$(=Year(Today())-1)}, Month = {\$(=Month(Today())-1)},Year=, Month=>} Amount))

I also need the acumulated amount from yearstart to monthend previous month...

Best regards

Torbjörn Ungvall (@Ungvall)

Tags (1)
1 Solution

Accepted Solutions
Valued Contributor III

## Re: Time in SET

Sum of amount from january to july (previous month):

Sum of amount from august last year to july (previous month):

Cheers!!

Jagan

11 Replies
Honored Contributor II

## Re: Time in SET

I think you don't have to "clear" it with ",Year=, Month=" in the end of your set expression.

Sum({\$<Year = {\$(=Year(Today()))}, Month = {\$(=Month(Today())-1)}>} Amount))

Not applicable

## Re: Time in SET

This can be done by putting the periods into date ranges.

Declare two variables

Then use an expression such as this to find the amount

sum({\$< DateMonth = {">=\$(#vDate12)<=\$(#vDateNow)"} >} Amount)

Hope that helps

Valued Contributor III

## Re: Time in SET

Hello,

Please check this file. My data looks like this:

Customer DateField Month Amount
A 01/05/2011 May 200
B 02/06/2011 Jun 300
C 03/07/2011 Jul 400
D 04/08/2011 Aug 500
A 01/05/2012 May 600
B 02/06/2012 Jun 700
C 03/07/2012 Jul 800
D 04/08/2012 Aug 900

Dimension as Customer

 Current Year- Previous Month:=Sum({}Amount)Previous Year-Previous month:=Sum({}Amount)

Cheers !!

Jagan

Not applicable

## Re: Time in SET

Works just fine - could I use the same for acumulated amount (yearstart to monthend previous month) and rolling 12 month, also to previous monthend?

Best regards

Torbjörn Ungvall (@Ungvall)

Valued Contributor III

## Re: Time in SET

Hi,

I didn't get you what you want. Can you explain with small example what you want exactly. I mean How is your data and what you need the output. Did you got any help from my previous post ? So that i can move forward.

Cheers!!

Jagan

Not applicable

## Re: Time in SET

It works just fine!

Can't give you example for various reasons.

I also need the acumulated amount this year:

Sum of amount from january to july (previous month)

and

The amount for rolling 12 month back:

Sum of amount from august last year to july (previous month)

Hope this helps!

Thanks/Toby

Contributor III

## Re: Time in SET

What I do for all these time period comparisons is this...

Sum({\$<\$(='[' & Concat({1<\$Table = {"MasterCalendar"}>}\$Field,'] = , [' ) & '] = '), MasterDate = {"\$(='>=' & MonthStart(Max(MasterDate),-1) & '<=' & AddMonths(Max(MasterDate),-1))"}>} SalesAmount)

So let's break this into parts

• \$(='[' & Concat({1<\$Table = {"MasterCalendar"}>}\$Field,'] = , [' ) & '] = ')

This part overrides all selections on the MasterCalendar table. The problem I always run into with this stuff is that YTD, MTD, Prior MTD and all other pre selected time slices you can think of need to be sensitive to the "current" selections of the user, if they are not handled properly you end up with charts/columns that go blank when the selections are not compatible. So overriding the selections on the calendar are the first part of preventing incompatible selections.

• MasterDate = {"\$(='>=' & MonthStart(Max(MasterDate),-1) & '<=' & AddMonths(Max(MasterDate),-1))"}

This part makes the selection on the primary date field for your data to match whatever range of dates you want. This example gives prior month to date. so if the user has selected July of 2012 you will see ALL of June if the user selects July 2012 up to July 15 then only June1 through June 15. There are lots of variations you can use based on adding and subtracting months or years from the min max or whatever dates are available.

I hope this helps

Chris

Valued Contributor III

## Re: Time in SET

Sum of amount from january to july (previous month):

Sum of amount from august last year to july (previous month):