Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
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.
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
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).
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
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)
)
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
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.
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)