7 Replies Latest reply: Aug 3, 2017 4:05 AM by Jose Miguel Vilaplana

# Show trend

Dear Qlik Users,

I have many issues .

I am trying to implend a trend for the receipt of files of the week selection compared with the last 10 weeks.

So I did not find solutions to :

• ignore the selection (I have three filters : Year, Month, Week) : I only care about the max(Date) of my selection.
• select 10 weeks.

Here is an example of a trend for the receipt of files last 3 days selection with the 21 days selection.

(

avg({\$<[Date Selection]={">=\$(=weekstart(max(Date Selection)))<\$(=weekend(max(Date Selection)+1))"}>} aggr(

count(distinct if([Date of receipt]) = [Date Selection], [File Number])),

Date))

-

avg({\$<[Date Selection]={">=\$(=date(max(Date)-20))<\$(=max(Date)+1)"}>} aggr(

count(distinct if([Date of receipt]) = [Date Selection], [File Number])),

Date))

)

/

avg({\$<[Date Selection]={">=\$(=date(max(Date)-20))<\$(=max(Date)+1)"}>} aggr(

count(distinct if([Date of receipt]) = [Date Selection], [File Numer])),

Date))

Thank you.

• ###### Re: Show trend

Hi,

To ignore the selection, change the \$ for a 1 in the set analysis

(

avg({1<[Date Selection]={">=\$(=weekstart(max(Date Selection)))<\$(=weekend(max(Date Selection)+1))"}>} aggr(

count(distinct if([Date of receipt]) = [Date Selection], [File Number])),

Date))

-

avg({1<[Date Selection]={">=\$(=date(max(Date)-20))<\$(=max(Date)+1)"}>} aggr(

count(distinct if([Date of receipt]) = [Date Selection], [File Number])),

Date))

)

/

avg({1<[Date Selection]={">=\$(=date(max(Date)-20))<\$(=max(Date)+1)"}>} aggr(

count(distinct if([Date of receipt]) = [Date Selection], [File Numer])),

Date))

And for the weeks, you can use the addweek function: https://community.qlikview.com/thread/16976

Regards

• ###### Re: Show trend

Thank you Jose Miguel,

Concerning a month or year selection, it works perfectly : it is ignoring selection.

But concerning a week, it does not work : the average for 21 days always calculates the average for the week selection (7 days).

• ###### Re: Show trend

Hi

You're selecting one week: ">=\$(=weekstart(max(Date Selection)))<\$(=weekend(max(Date Selection)+1))"

Try with ">\$(=addweeks(max(Date Selection), -3))<\$(=max(DateSelection))"

This will take the last 3 weeks

• ###### Re: Show trend

I have tried it but it seems that the function addweeks do not exist.

Jose Miguel, apparently the issue comes from the count.
It is hard to find a solution that calculate the average of the last 10 weeks, ignoring every selections but the max(Date Selection) because I need it as a starting point :

// here is the average for the last 21 days.

avg({1<[Date Selection]={">=\$(=date(max(Date)-20))<\$(=max(Date)+1)"}>}

aggr(

count(distinct if([Date of receipt]) = [Date Selection], [File Number])),

Date)

)

• ###### Re: Show trend

I'm sorry, it's true addweeks doesn't exist

And what about the average, it works now? With 21 days?

Probably you must to add the same Set Analysis to the Count function

• ###### Re: Show trend

Well, it doesn't work neither for 21 days.
I still have the same problem.

I know precisely where the issue is :

count ( {<Date={">=\$(=date(max(Date)-20))<\$(=max(Date)+1)"}> } Date)

This formula should give 21 as a result but when I select for example a week, it keeps giving me 7.

• ###### Re: Show trend

Hi

Is because you need to put the 1 before the set analysis, if not, you're applying the actual selection.

Try:

count ( {1<Date={">=\$(=date(max(Date)-20))<\$(=max(Date)+1)"}> } Date)