topic Re: Total Number of Week Count? in QlikView

Total Number of Week Count?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Total Number of Week Count?

MK_QSL — Mon, 23 Jan 2017 07:08:29 GMT

Try this.

=(Date(Max(TOTAL{<Article>}Date)) - Date(Min(TOTAL <Article> {<Article = {'*'}>}Date)))/7

Re: Total Number of Week Count?

MK_QSL — Mon, 23 Jan 2017 07:10:01 GMT

You have Max Date 18/01/2017, then how come for C you have 2 weeks coming?

Re: Total Number of Week Count?

devarasu07 — Mon, 23 Jan 2017 07:33:46 GMT

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

Re: Total Number of Week Count?

MK_QSL — Mon, 23 Jan 2017 09:02:49 GMT

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

Re: Total Number of Week Count?

devarasu07 — Mon, 23 Jan 2017 09:53:36 GMT

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

Re: Total Number of Week Count?

MK_QSL — Mon, 23 Jan 2017 10:02:45 GMT

Can you elaborate with an example please?

Re: Total Number of Week Count?

sunny_talwar — Mon, 23 Jan 2017 10:56:44 GMT

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)