month to month year prior.

[dries_clairhout]

Hello everyone,

For a Client of mine we set up a Activity analysis sheet for the warehoue.

Now the client wants to see the activities of the personel of the month compared to the month year prior.

This is in Qlik Sense,

I did this formula.

=avg(aggr(sum({<Month= = {"=$(=Max(Month))"} ($(vColli))/$(vDag), Datum, magazijnier))

But it doesn't display anything.

The normal formula (displays based on selection):

=avg(aggr(sum($(vColli))/$(vDag), Datum, magazijnier))

Explenation fields:

Datum (Date)

Magazijnier (employee)

vDag (Day)

vColli (parcel)

Thanks in advance.

[PabloTrevisan]

Hi ! Could you try to implement in your sintaxe the function total &lt; &gt; and use set analysis to force conditions ?

Avg([{SetExpression}] [DISTINCT] [TOTAL []] expr) 

[dries_clairhout]

Hello,

I'm setting my first steps in data set analysis,

could you give it with my code?

Thank you.

[sunny_talwar]

You seem to have an extra = signs in there and you have not closed your set analysis

=Avg(Aggr(Sum({<Month= = {"=$(=Max(Month))"} ($(vColli))/$(vDag), Datum, magazijnier))

Try this and see if this does what you are looking to get

=Avg(Aggr(Sum({<Month = {"$(=Max(Month))"}>} ($(vColli))/$(vDag), Datum, magazijnier))

[dries_clairhout]

Hello,

Thank you for the correction.

I still get an empty graph after doing this sadly enough.

[sunny_talwar]

How do you create Month field in the script? Is it created using Month function? or is it read as string by Qlik?

[dries_clairhout]

This is the calendar I build:

min_max:
Load
min(%Kalender) as minDate,
max(%Kalender) as maxDate
Resident Picking;

Let vMinDate = Peek('minDate', 0, 'min_max')-1;
Let vMaxDate = Peek('maxDate', 0, 'min_max');
Let vGenerate = round(Peek('maxDate', 0, 'min_max') - $(vMinDate));

DROP Table min_max;


Kalender:
Load *,
TempDate AS %Kalender,
Year(TempDate)&Ceil (Month(TempDate)/3) as JaarKwartaal,
date(TempDate) As Datum,
week(TempDate) As Week,
Year(TempDate) As Jaar,
Month(TempDate) As Maand,
num(month(TempDate), '00' ) As Periode,
Day(TempDate) As Dag,
date(monthstart(TempDate), 'MMM-YYYY') As MaandJaar,
num#(date(monthstart(TempDate), 'YYYYMM') ) As JaarPeriode,
(Year(TempDate) * 12) + num(month(TempDate), '00' ) as JaarPeriodeID,
'Q' & Ceil (Month(TempDate)/3) As Kwartaal,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) As WeekJaar,
WeekDay(TempDate) As WeekDag,
if(TempDate = $(vMaxDate)
, 1
) As MaxDateFlag,
if(MakeDate(2000, month(today()), day(today())) >= MakeDate(2000, month(TempDate), Day(TempDate))
, 'YTD'
) As YTD
;

LOAD
$(vMinDate) + RecNo() As TempDate
autoGenerate $(vGenerate);

[sunny_talwar]

How about this?

=Avg(Aggr(Sum({<Month = {"$(=MaxString(Month))"}>} ($(vColli))/$(vDag), Datum, magazijnier))

If this still doesn't work... would you be able to provide a sample to look at the issue?

[dries_clairhout]

Hello it doesn't work,

what kind of a sample do you want?

Thanks in advance

Dries

[sunny_talwar]

Either few rows of raw data or a sample app where you are doing this. It would be good to know what you are hoping to get as an output based on the sample you provide