# Water Cooler

This space is for everyone to ask questions related to the Community Platform. It's a space for us to get to know each other and have some fun! Come in and gather around the Water Cooler!

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:  Creator

## month to month year prior.

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)

14 Replies  Partner - Creator II

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)  Creator
Author

Hello,

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

could you give it with my code?

Thank you.  MVP

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))`  Creator
Author

Hello,

Thank you for the correction.

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

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

This is the calendar I build:

min_max:
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:
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
;

\$(vMinDate) + RecNo() As TempDate
autoGenerate \$(vGenerate);  MVP

`=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?  Creator
Author

Hello it doesn't work,

what kind of a sample do you want?   