topic Re: Show trend in App Development

Show trend

m_m_fr — Wed, 02 Aug 2017 08:06:14 GMT

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

jmvilaplanap — Wed, 02 Aug 2017 08:57:49 GMT

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

m_m_fr — Wed, 02 Aug 2017 09:36:36 GMT

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

jmvilaplanap — Wed, 02 Aug 2017 09:58:46 GMT

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

m_m_fr — Wed, 02 Aug 2017 12:51:27 GMT

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

jmvilaplanap — Wed, 02 Aug 2017 12:55:29 GMT

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

m_m_fr — Wed, 02 Aug 2017 15:43:50 GMT

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

jmvilaplanap — Thu, 03 Aug 2017 08:05:16 GMT

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)