Qlik Community

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
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
dries_clairhout
Creator
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)

 

Thanks in advance.

 

14 Replies
PabloTrevisan
Partner - Creator II
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) 

dries_clairhout
Creator
Creator
Author

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

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

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

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