topic Re: Last Week Median Calculation using aggr() in QlikView

Last Week Median Calculation using aggr()

Tue, 15 Dec 2015 09:55:31 GMT

Dear All,

i have a Member Name Dimension and Date and WeekStartDate and Turnover and DealersCount

my requirement is to get Previous Week datewise Median Dealer count.

i have used below mention set analysis to get information :

Median(aggr(sum({<DATE1=,WEEKSTARTDATE={"$(=date(max(WEEKSTARTDATE)-7,'dd-MMM-yyyy'))"}>} DEALERCOUNT),NAME,DATE1 ))

when i am not selecting any DATE1 then its working fine, but when i select any DATE1, its did not give me the result.

Kindly help.

i am sending you attached excel and qvw for same.

note: my actual table have more dimension and columns.

thanks & Regards,

Jitendra Kumar Vishwakarma

Re: Last Week Median Calculation using aggr()

avinashelite — Tue, 15 Dec 2015 10:05:01 GMT

Try like this , create week field in your script ( with the year column if required). then use this field in the front end calculation

week(date)

Week number. Returns an integer representing the week when the fraction of expr is interpreted as a date according to the standard number interpretation.

Example:

week( '1971-10-30' ) returns 43.

Re: Last Week Median Calculation using aggr()

Tue, 15 Dec 2015 10:10:54 GMT

Instead of Week Number i have already Stored WeekStartDate which is similar to that.

Please check the QVW and Excel file you will get the idea what i am looking for.

Thanks

Jitendra kumar Vishwakarma

Re: Last Week Median Calculation using aggr()

Tue, 15 Dec 2015 10:19:27 GMT

I think sir, i have not explain my requirement properly.

My requirement is when ever i select any date (if not select any date take max), goto previous week of that date. get all datewise dealer count and then get median of that.

aggr() will help to get NAME,DATE1 wise Dealercount in NAME dimension table.

aggr( sum(dealercount),NAME,DATE1) but for previous week datewise i have used this :

aggr(sum({<DATE1=,WEEKSTARTDATE={"$(=date(max(WEEKSTARTDATE)-7,'dd-MMM-yyyy'))"}>} DEALERCOUNT),NAME,DATE1 )

and on top of this i put median() so i can get the value.

when i didnot select any date its working fine.... but if i select and particular date then the syntax is not able to get previous week dates according to selection.

Re: Last Week Median Calculation using aggr()

avinashelite — Tue, 15 Dec 2015 10:36:32 GMT

Hi Jitendra,

I checked your file , here is my observation:

1.Your try to calculate the last week observation on the same day i.e current week Monday what was the count and last week Monday what was the count, in this case your expression is working fine but in your dashboard you does not have the data select 14 Nov you will get the result because 14 -7 = 7 and you have this date

2. If you want to use AGGR then those value has to be in dimension i.e

aggr(sum({<DATE1=,WEEKSTARTDATE={"$(=date(max(WEEKSTARTDATE)-7,'dd-MMM-yyyy'))"}>} DEALERCOUNT),NAME,DATE1 )

here you have aggregating on Name and DATE1 but NAME is in the dimension but not the DATE1 hence it will not work

Re: Last Week Median Calculation using aggr()

Tue, 15 Dec 2015 11:04:02 GMT

Hi,

i have full last week Data.

i am not trying to calculate current Monday with previous Monday.

if i subtract 14-nov-2015 to 7 then i will get 07-nov-2015. that week is already there.

because i want name, date1 wise aggregation thats why i needs to add that too in it.

FULL DATA

NAME	DATE1	WEEKSTARTDATE	TURNOVER	DEALERCOUNT
ABB	07-Nov-2015	07-Nov-2015	125	3
ABB	08-Nov-2015	07-Nov-2015	450	3
ABB	09-Nov-2015	07-Nov-2015	350	6
ABB	10-Nov-2015	07-Nov-2015	300	6
ABB	11-Nov-2015	07-Nov-2015	100	8
ABC	07-Nov-2015	07-Nov-2015	235	1
ABC	08-Nov-2015	07-Nov-2015	200	4
ABC	09-Nov-2015	07-Nov-2015	200	4
ABC	10-Nov-2015	07-Nov-2015	150	4
ABC	11-Nov-2015	07-Nov-2015	250	4
ABB	14-Nov-2015	14-Nov-2015	100	8
ABC	14-Nov-2015	14-Nov-2015	200	5

1). if i am not selecting any date1 then my max date1 is 14-nov-2015 and also my max weekstartdate is 14-nov-2015.

2). if i subtract 14-nov-2015 with 7 then i will get 07-nov-2015.

3). now what i will do is memberwise date1wise all the dealer count like this :

for ABB : (LAST WEEK)

NAME	DATE1	WEEKSTARTDATE	TURNOVER	DEALERCOUNT
ABB	07-Nov-2015	07-Nov-2015	125	3
ABB	08-Nov-2015	07-Nov-2015	450	3
ABB	09-Nov-2015	07-Nov-2015	350	6
ABB	10-Nov-2015	07-Nov-2015	300	6
ABB	11-Nov-2015	07-Nov-2015	100	8

for ABC : (LAST WEEK)

NAME	DATE1	WEEKSTARTDATE	TURNOVER	DEALERCOUNT
ABC	07-Nov-2015	07-Nov-2015	235	1
ABC	08-Nov-2015	07-Nov-2015	200	4
ABC	09-Nov-2015	07-Nov-2015	200	4
ABC	10-Nov-2015	07-Nov-2015	150	4
ABC	11-Nov-2015	07-Nov-2015	250	4

Now if you do Median on that displayed dealer count data of ABB you will get 6 and for ABC you get 4.

Displaying Current Week Turnover along with last Week Turnover and Median Dealer count.

NAME	Current Date Turnover	Last Week Turnover	Last Week Median Dealer Count
	300	2360	4
ABB	100	1325	6
ABC	200	1035	4

when i did not select any DATE1 its take max DATE1 and do all this which is working fine.

but if i select DATE1 as '14-Nov-2015' which is equivalent to max(DATE1) but its did not work because DATE1 Dimension get into the effect.

i have try to by pass DATE1 Dimension in Aggr() Set Analysis but then also its not working.

Median(aggr(sum({<DATE1=,WEEKSTARTDATE={"$(=date(max(WEEKSTARTDATE)-7,'dd-MMM-yyyy'))"}>} DEALERCOUNT),NAME,DATE1 ))

Hope now you will understand what actually i want and what i am doing.

Thanks

Jitendra Kumar Vishwakarma

Re: Last Week Median Calculation using aggr()

Tue, 15 Dec 2015 11:15:29 GMT

Hi,

Try this that aggr function will not be associated with the current selection.

Median({<DATE1=}>}aggr(sum({<DATE1=,WEEKSTARTDATE={"$(=max(date(WEEKSTARTDATE-7,'dd-MMM-yyyy')))"}>} DEALERCOUNT),NAME,DATE1 ))

Regards

Gilles

Re: Last Week Median Calculation using aggr()

MayilVahanan — Tue, 15 Dec 2015 11:35:29 GMT

May be try like this

Median({<DATE1=,WEEKSTARTDATE={"$(=date(max(WEEKSTARTDATE)-7,'dd-MMM-yyyy'))"}>}aggr(sum({<DATE1=,WEEKSTARTDATE={"$(=date(max(WEEKSTARTDATE)-7,'dd-MMM-yyyy'))"}>} DEALERCOUNT),NAME,DATE1 ))