QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

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

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

Kind Regards,

Miles

Tags (4)
1 Solution

Accepted Solutions
Valued Contributor

Re: Dates in Set Analysis

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.

Regards

Kamal

8 Replies
MVP

Miles

Try these

Hope that helps

Jonathan

Highlighted
Not applicable

Re: Dates in Set Analysis

Hi Jonathan,

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

Kind Regards,

Miles

MVP

Re: Dates in Set Analysis

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

MVP

Re: Dates in Set Analysis

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

Not applicable

Re: Dates in Set Analysis

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

Valued Contributor

Re: Dates in Set Analysis

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

Kamal

Not applicable

Re: Dates in Set Analysis

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

Valued Contributor

Re: Dates in Set Analysis

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.