topic Re: Average 30 last days for each date selected in QlikView

Average 30 last days for each date selected

Mon, 12 Oct 2015 12:11:02 GMT

Hello Everyone,

I will give you an example if you can help me to resolve my problem. i was not able to resolve the problem since one week

I have to compare the number of transactions of one day to last 3 days.

for the date i have a field : Date_Appli

22/09/2015

23/09/2015

24/09/2015

25/09/2015

The field dimension is "primary_asset_class" which has 5 values

PRIMARY_ASSET_CLASS	= sum(FLAG_DTCC_EOD_SENT)	Average
	5469306	273,034
Equity	1600973	400,386
InterestRate	1583450	394,698
ForeignExchange	1156549	288,403
Credit	733281	183,049
Commodity	395053	98,635

to calculate the average i have used this expression :

=Avg(aggr(sum({<DATE_APPLI={">=$(MinDate) < $(MaxDate)"}>} FLAG_DTCC_EOD_SENT),PRIMARY_ASSET_CLASS,DATE_APPLI))

For information i have defined the variables used in expression like this :

MaxDate =Floor(max(DATE_APPLI))

MinDate = MaxDate -3

Normaly when i select 25/09/2015 (Sept 25th 2015) i will get in the the ceil "average" the average of the transaction of 3 previous days (Sept 24th, sept 23th and sept 22th). but it not working --> it shows this :

PRIMARY_ASSET_CLASS	= sum(FLAG_DTCC_EOD_SENT)	Average
	1373791	-
Equity	399815	-
InterestRate	399355	-
ForeignExchange	291340	-
Credit	184133	-
Commodity	99148	-

Thank you very much in advance for your help

Regards,

Re: Average 30 last days for each date selected

swuehl — Mon, 12 Oct 2015 13:40:06 GMT

Have you formatted the variables correctly as dates?

Dates in Set Analysis

What does your expression show after expanding the variables (you can see this when you remove the expression label, then hover with the mouse over the chart expression label)?

Re: Average 30 last days for each date selected

Mon, 12 Oct 2015 14:30:46 GMT

The field for date (date_appli ) is formated as numeric,

the expression show : date_appli >=42247 <=42277.

the expression calculate correctly the average for all dates. but it doasn't work when i select one date.

I have defined the format of date_appli in report propertise as numerci in the variable also the format is numerci.

Normaly no problem with the comparaison

Regards

Re: Average 30 last days for each date selected

swuehl — Mon, 12 Oct 2015 14:37:08 GMT

Are you selecting on calendar fields other than date_appli (i.e. another field with date granularity)?

You may end up with an incompatible set definition then, hence you need to clear all selections in calendar fields, like AnotherDate, Month, Year, etc.

sum({<DATE_APPLI={">=$(MinDate) < $(MaxDate)"}, AnotherDateField= >}FLAG_DTCC_EOD_SENT)

You may also need to apply the set expression to the outer aggregation:

=Avg({<DATE_APPLI={">=$(MinDate) < $(MaxDate)"}, AnotherDateField= >}

aggr(sum({<DATE_APPLI={">=$(MinDate) < $(MaxDate)"}, AnotherDateField= >} FLAG_DTCC_EOD_SENT),PRIMARY_ASSET_CLASS,DATE_APPLI))

In general, it's easier to help if you post a small sample QVW that shows your issue, since there might be different places needed to look at.

Re: Average 30 last days for each date selected

Mon, 12 Oct 2015 14:48:42 GMT

Dear Gabrie ,

I understood your problem , to resolve this create dummy field for Date_Appli and make format of that date to number

like as below

Load .

,Date_Appli

Num(Date_Appli) as NewDate

After that just make small change in your existing expression ,

=Avg(aggr(sum({<NewDate={">=$(MinDate) < $(MaxDate)"}>}FLAG_DTCC_EOD_SENT),PRIMARY_ASSET_CLASS,DATE_APPLI))

Hope this will work , Happy Learning .

-Nitesh Chavan

Re: Average 30 last days for each date selected

Mon, 12 Oct 2015 16:08:51 GMT

Dear Chavane,

this is what i have already done, see the code below :

SUB srGenerateCalendar(pCalendarName, pDateFieldName)

$(pCalendarName):

LOAD

Num( InvoiceDate) AS [$(pDateFieldName)],

...etc.

Knowing that (pDateFieldName) = date_appli

thank you for your suggestion.

Regards

Re: Average 30 last days for each date selected

Mon, 12 Oct 2015 16:10:44 GMT

Dear swuehl,

I will try to post a sample QVW.

Regards

Re: Average 30 last days for each date selected

JonasValleskog — Mon, 12 Oct 2015 22:39:28 GMT

Hi Gabriel,

Your most likely issue is that you need to reflect any set analysis condition in both the outer and inner aggregation functions. Currently, your Avg() function is subject to your current selections (i.e. {$}) so when you select a DATE_APPLI your outer function will restrict the average to act upon only the date selected. I wouldn't have expected NULL back as your results however, but never the less, my educated guess is that you will get your desired result by copying the set analysis statement from the inner aggregation function sum() to the outer aggregation function avg() like this:

Avg({<DATE_APPLI={">=$(MinDate)<$(MaxDate)"}>} aggr(sum({<DATE_APPLI={">=$(MinDate)<$(MaxDate)"}>}FLAG_DTCC_EOD_SENT),PRIMARY_ASSET_CLASS,DATE_APPLI))

If that still doesn't help - create a pivot table with two dimensions: PRIMARY_ASSET_CLASS,DATE_APPLI and one expression: sum({<DATE_APPLI={">=$(MinDate)<$(MaxDate)"}>}FLAG_DTCC_EOD_SENT) with Presentation option 'always fully expanded' ticked and inspect the results. This is effectively a representation of your "invisible" table of data returned by the aggr() function to the outer Average aggregation function. If you can't spot the issue still - try building a List Box of your DATE_APPLI field and manually test the Set Analysis filter condition by searching for: >=42247<=42277.

By now you should have found your issue.

I hope it helps.

Regards

Jonas

Re: Average 30 last days for each date selected

Anders_Eriksson — Tue, 13 Oct 2015 07:33:36 GMT

The answers you already got covers most common problems with this kind of expression.

If you still can't make it work please post a sample application with your problem because then it sounds as you have not explained it properly or are omitting some vital detail.

Re: Average 30 last days for each date selected

Tue, 20 Oct 2015 12:55:14 GMT

hello all,

Thank you very much for your answer ..

the correct exepression for my porjetc (not for the example i have done in questionis) is :

Avg({1} aggr(Count( {1<[Application date]={">=$(vMinDate)<$(vMaxDate)"}, DealValo={1}>} Distinct RowNo_VIP_REP_DTCC_EOD_SENT),PRIMARY_ASSET_CLASS,[Application date]))

Regards

Re: Average 30 last days for each date selected

JonasValleskog — Tue, 20 Oct 2015 13:13:36 GMT

Hi Gabriel,

Can you verify if this expression isn't per chance giving the same results?

I see no reason why your outer aggregation function

Avg({1} .... )

should not actually be written as:

Avg({1<[Application date]={">=$(vMinDate)<$(vMaxDate)"}, DealValo={1}>} ....)

The inner scope inside your count is restricted, so reasonably the outer scope of your average should follow suit. You'll likely get the same results from both, I'm simply trying to promote a principal of syntax implementation that works in all scenarios - i.e. something that could be called "Best Practice". My version will likely perform marginally better, as the outer aggregation scope is now unnecessarily wide.

Regards

Jonas