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: 
m_m_fr
Contributor III
Contributor III

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.

7 Replies
jmvilaplanap
Specialist
Specialist

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

m_m_fr
Contributor III
Contributor III
Author

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

jmvilaplanap
Specialist
Specialist

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

m_m_fr
Contributor III
Contributor III
Author

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)

     )



jmvilaplanap
Specialist
Specialist

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

m_m_fr
Contributor III
Contributor III
Author

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.

jmvilaplanap
Specialist
Specialist

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)