topic Re: Time in SET in QlikView

Time in SET

Thu, 30 Aug 2012 08:10:16 GMT

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)

Senior Business Discovery Manager

Advectas AB

Re: Time in SET

whiteline — Thu, 30 Aug 2012 08:24:08 GMT

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))

Re: Time in SET

Thu, 30 Aug 2012 08:31:12 GMT

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

Declare two variables

vDateNow =date(addmonths(Date_MonthYear,-1),'MMM-YYYY')
vDate12 =date(addmonths(Date_MonthYear,-12),'MMM-YYYY')

Then use an expression such as this to find the amount

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

Hope that helps

Re: Time in SET

jagannalla — Thu, 30 Aug 2012 09:01:12 GMT

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({<Year={$(=Year(Today()))},Month={$(=Month(AddMonths(Today(),-1)))}>}Amount)
Previous Year-Previous month:
=Sum({<Year={$(=Year(Today())-1)},Month={$(=Month(AddMonths(Today(),-1)))}>}Amount)

Cheers !!

Jagan

Re: Time in SET

Thu, 30 Aug 2012 13:01:28 GMT

Thanks for your time!

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)

Senior Business Discovery Manager

Advectas AB

Re: Time in SET

jagannalla — Thu, 30 Aug 2012 13:11:22 GMT

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

Re: Time in SET

Thu, 30 Aug 2012 13:29:26 GMT

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

Re: Time in SET

chriscammers — Fri, 31 Aug 2012 12:42:48 GMT

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

Re: Time in SET

jagannalla — Fri, 31 Aug 2012 13:52:28 GMT

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

=Sum({$<DateField={">=$(=Date((YearStart(Today())),'DD/MM/YYYY'))<=$(=Date(MonthEnd(AddMonths(Today(),-1)),'DD/MM/YYYY'))"}>} Amount)

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

Sum({$<DateField={">=$(=Date(MonthStart(AddMonths(Today(),-12)),'DD/MM/YYYY'))<=$(=Date(MonthEnd(AddMonths(Today(),-1)),'DD/MM/YYYY'))"}>} Amount)

Cheers!!

Jagan

Re: Time in SET

Fri, 31 Aug 2012 13:54:10 GMT

Thanks a lot! have a great weekend...

Re: Time in SET

chriscammers — Fri, 31 Aug 2012 13:54:27 GMT

Careful Jagan,

Your solution will fall victim to incompatible selections

Re: Time in SET

Fri, 31 Aug 2012 13:58:39 GMT

I can handle that by

Year=, Month=

Best regards/Toby