Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm trying to calculate how many weeks ago each article was lunched? it should be based on user selected Week and Year filter
I've used below formula,
=(Date(Max(TOTAL{<Article>}Date)) - Date(Min(TOTAL <Article> {<Article = {'*'}>}Date)))/7
Below is the excel formula which is giving correct output, i'want something similar in qlikview?
SUM((Current.Year - Article Launched Year)*53,(Current Week - Article Launched Week),1)+1
Thanks,
Deva
RangeSum((Year(Date(Max(TOTAL{<Article>}Date))) - Year(Date(Min(TOTAL <Article> {<Article = {'*'}>}Date)))) * 53
,Week(Date(Max(TOTAL{<Article>}Date))) - Week(Date(Min(TOTAL <Article> {<Article = {'*'}>}Date))),1) + 1
Try this.
=(Date(Max(TOTAL{<Article>}Date)) - Date(Min(TOTAL <Article> {<Article = {'*'}>}Date)))/7
You have Max Date 18/01/2017, then how come for C you have 2 weeks coming?
Hi Manish,
Thanks, I've tried your expression and it's not matching with expected output and also if we select any year & week filter then the expression gives wrong result? Total No.of Weeks difference based on user selected year and week from list box,
Please refer to the attached excel calculation and expected output.
You have Max Date 18/01/2017, then how come for C you have 2 weeks coming?
Yeah your right, it should fall on under week 3.
Thanks, Deva
RangeSum((Year(Date(Max(TOTAL{<Article>}Date))) - Year(Date(Min(TOTAL <Article> {<Article = {'*'}>}Date)))) * 53
,Week(Date(Max(TOTAL{<Article>}Date))) - Week(Date(Min(TOTAL <Article> {<Article = {'*'}>}Date))),1) + 1
Hi Manish,
How to exclude Week & year filter selection from your expression? No of Week launched should stick to user selected year and week filter.
RangeSum((Year(Date(Max(TOTAL{<Article>}Date))) - Year(Date(Min(TOTAL <Article> {<Article = {'*'},Week=,Year=>}Date)))) * 53
,Week(Date(Max(TOTAL{<Article>}Date))) - Week(Date(Min(TOTAL <Article> {<Article = {'*'},Week=,Year=>}Date))),1)
Regards, Deva
Can you elaborate with an example please?
May be ignore them for Max also
RangeSum((Year(Date(Max(TOTAL{<Article, Week, Year>}Date))) - Year(Date(Min(TOTAL <Article> {<Article = {'*'},Week=,Year=>}Date)))) * 53
,Week(Date(Max(TOTAL{<Article, Week, Year>}Date))) - Week(Date(Min(TOTAL <Article> {<Article = {'*'},Week=,Year=>}Date))),1)