Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dates in Set Analysis

Hi Everyone,

2 parts to this post, Part 1:

I have a fiedl, [1L Date], which is a date field. I am using the following Set Analysis expression

=SUM({<[1L Date]={"<=DATE(AddYears(today(),-1))"}>} quantity)

I am trying to get the sum of all quntities in the last 365 days

Part 2

Same as Part 1, except I want the Sum of all Quantities from the previous 365 days so something like

=SUM({<[1L Date]={">=DATE(AddYears(today(),-2))", [1L Date]={"<=DATE(AddYears(today(),-1))"}>} quantity)

Both expressions are returning 0 at the minute, please can someone help,

Kind Regards,

Miles

1 Solution

Accepted Solutions
kamalqlik
Partner - Specialist
Partner - Specialist

Hi Miles,

You can use the addmonths() in case you want to get the sum for the Prior 12 months or more.

//Num#(Sum({<YearMonth1 ={">=$(=Date(addmonths(Max(YearMonth1), -11), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth1), 0),  'MMM-YY'))"} >}Quantity)).

This will give the sum for last one year sale from Todays date.

First Calculate YearMonth in the Script where you are calculating Year and other date field

(Month(date)&'-'& right(Year(date),2)) as YearMonth,

and than take the resident of that table,

and calculate

YearMonth1

Date(Date(Date#(YearMonth,'MMM-YY'),'DD-MM-YYYY'),'MMM-YY') as YearMonth1.

This is done because we want the YearMonth Format to be in number as used in the Front-End.

Hope this help you

Regards

Kamal

View solution in original post

8 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Miles

Try these

=Sum({<[1L Date]={"<=$(=DATE(AddYears(today(),-1)))"}>} quantity)

=Sum({<[1L Date]={">=$(=DATE(AddYears(today(),-2))) <=$(=DATE(AddYears(today(),-1)))"}>} quantity)

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi Jonathan,

Thanks for your reply, unfortunately this still comes up with a 0, any more ideas?

Kind Regards,

Miles

Miguel_Angel_Baeyens

Hello Miles,

Does those fields have an actual (numeric) date field so the value returned by the function Date() is exactly the same as the stored in the field? Is there any chance that there might be some differences because of the regional settings in Windows?

Miguel

jonathandienst
Partner - Champion III
Partner - Champion III

Miles

Where are you trying to use these expressions - in a text box or in a dimensioned chart - the above will work in the former, but possibly not in the latter depending on your data model and the chart dimensions?

And are you selecting any date related fields? That field selection may be limiting the data returned.

Have you tested the expression with literal dates (work out the date corresponding to the calculation, eg

     =Sum({<[1L Date]={"<=2011/10/11"}>} quantity)

Finally, do you actually have data in the date ranges being selected?

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi guys,

Thank you for your support. I have been playing around with trying to get these values to work in a textbox first before jumping ahead and putting it into a table, but I got this formula to work,

=sum({1<[1L Date]={">=$(=addyears(max([1L Date]=))-3) <=$(=min([1L Date]))"}>}quantity)

BUT I don't know why this works and Jonathan's suggestion didn't, please can someone explain so I can learn?

P.S. in terms of regional settings, it is a possibility that something like this might be happening as I am dragging data over from a US server...it could be an explanation, in which case how would you recommend I combat that?

Kind Regards,

Miles

kamalqlik
Partner - Specialist
Partner - Specialist

Hi Miles,

Just try like this....

In case you want to calculate YTD-

=num(sum({<[Month]=,[Year]={$(=max([Year]))},[Date]=

{"<=$(vCurrDate)"}>}[Stock]))

where vCurrDate is a varible and it's value will be --date(today()-1) or date (today())

Again

for LYTD

=num(sum({<[Month]=,[Year]={$(=max([Year])-1)},[Date]=

{"<=$(vCurrDate)"}>}[Stock]))

Hope this help you

Regrads

Kamal

Not applicable
Author

Hi Kamal,

That's great in terms of last 12 months to date, but in terms of getting the previous 12 months prior to that why is it when I change the formula to this:

=num(sum({<[Month]=,[Year]={$(=max([Year])-2)},[Date]={"<=$(vPriorYearDate)"}>}quantity))

Where vPriorYearDate is todays date minus 1 year,

it doesn't work...any ideas?

Kind Regards,

Miles

kamalqlik
Partner - Specialist
Partner - Specialist

Hi Miles,

You can use the addmonths() in case you want to get the sum for the Prior 12 months or more.

//Num#(Sum({<YearMonth1 ={">=$(=Date(addmonths(Max(YearMonth1), -11), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth1), 0),  'MMM-YY'))"} >}Quantity)).

This will give the sum for last one year sale from Todays date.

First Calculate YearMonth in the Script where you are calculating Year and other date field

(Month(date)&'-'& right(Year(date),2)) as YearMonth,

and than take the resident of that table,

and calculate

YearMonth1

Date(Date(Date#(YearMonth,'MMM-YY'),'DD-MM-YYYY'),'MMM-YY') as YearMonth1.

This is done because we want the YearMonth Format to be in number as used in the Front-End.

Hope this help you

Regards

Kamal