Min and Max calculations for each month (Calcul du Min & Max pour chaque mois)

[#1]

Hello,

CJ extract one of my excel file (http://dl.free.fr/mN8UKVchY)

I would like for each date the number of maximum & minimum sales per day to create a color chart (http://blogbi.asi.fr/2013/06/07/delicieuse-recette-de-nuancier-calendaire-avec- QlikView /)

I tried to count the max / min value but it does not work:

LOAD

    1 as Nb,

     day ([Sale Date]), Days) as Days

     max (aggr (Sum (Nb), Days) as maxvaleur,

     min (aggr (Sum (Nb), Days) as minvaleur,

Thank you for your help

Translated with Google Translate - Qlik Community Administrative Team

Bonjour,

CJ un extract de mon fichier excel (http://dl.free.fr/mN8UKVchY)

J'aimerais avoir pour chaque date le nombre de vente maximum & minimum par jours afin de créer un nuancier (http://blogbi.asi.fr/2013/06/07/delicieuse-recette-de-nuancier-calendaire-avec-qlikview/)

J'ai essayé de compter les max/min valeur mais ça ne fonctionne pas :

LOAD
   1 as Nb,
    day([Date Vente]),Jours) as Jours,

    max(aggr(Sum(Nb),Jours) as maxvaleur,

    min(aggr(Sum(Nb),Jours) as minvaleur,

Merci pour votre aide

[oknotsen]

Sorry, my translation skills and tools are limited. As a result I am having trouble downloading your example data. Also the link for the chart you want to make goes to a page with a LOT of charts and I do not want to guess what chart you want to make.

Based on the script example you are giving, I think you are having trouble getting that specific script to work.

Here is my suggestion for that script:

LOAD

    day([Date Vente]),Jours) as Jours,

    max(aggr(Sum(Nb),Jours) as maxvaleur,

    min(aggr(Sum(Nb),Jours) as minvaleur

Resident SomeTableThatIsAlreadyLoaded

group by Jours;

If that solves your problem, please flag the topic as answered.

If not, please post or attach some example data to this post and please describe the chart you are trying to make.

Good luck!

[#3]

No problem, we can switch in English, my example data :

Commande Number  | Date Vente               |

KSI036585                  | 22/08/2014  09:05:08

KSI021252                  | 01/08/2014  10:51:57

KSI019606                  | 11/08/2014  14:31:08

KSI020262                  | 19/08/2014  09:14:09

KSI019526                  | 11/08/2014  09:11:54

In my example, maxvaleur = 2 (on 11/08) and 1 as minvaleur

My loading script :

LOAD

  1 as sales,

  Month([Opened]) as Mois,

  Day([Opened]) as Jours,

  max(Sum(incident),Jours) as maxvaleur,

  min(Sum(incident),Jours) as minvaleur

FROM

[data.xls]

(biff, embedded labels);

but i got an error : Error in expression: Nested aggregation not allowed

I try your script but it seems not working.

Thanks

[Agis-Kalogiannis]

The reason you are getting this error is because you have removed the agg() function from your script, having as a result a nested aggregation:

[#5]

Thank's for your quick reply.

I modify my script as you specify but i got an error : Error in expression: AGGR is not a valid function

LOAD

  1 as sales,

  Month([Date Vente]) as Mois,

  Day([Date Vente]) as Jours,

  max(aggr(Sum(sales),Jours) as maxvaleur,

  min(aggr(Sum(sales),Jours) as minvaleur

FROM

[data.xls]

(biff, embedded labels);

[Anonymous]

Hi,

A few things are wrong with this syntax. When aggregating in the script you need to use the Group By clause. Correct me if I'm wrong but the aggr function is not available not available in the script but instead used in the UI. Also you are referring to the field name "Jours" even if the aggr would work you would have to refer to the Day([Date Vente]) instead.

I'm multitasking at the moment so I might have missed something.

[#7]

Hi,

How i can do if we can't use the aggr function  in the script ?

LOAD

  1 as sales,

  Month([Date Vente]) as Mois,

  Day([Date Vente]) as Jours

FROM

[data.xls]

(biff, embedded labels);

and specify the max & min in expression ?

[oknotsen]

I think you have to do it with two tables.

Have to admit I did not test-run it.

TempTable:

LOAD

    day([Date Vente]),Jours) as Jours,

    Month([Date Vente]) as Mois,

    Sum(Nb) as sumNb

FROM

[data.xls]

(biff, embedded labels)

group by Jours, Mois;

Load

Jours,

max(sumNb) as MaxNb,

min(sumNb) as MinNb

resident TempTable

group by Jours;

drop table TempTable;

[#9]

Hello,

i try your solution but it's not working so i try with 2 temp table but it's doesn't work :

Temp:

Load

    Month([Date Vente]) as Mois,

    Day([Date Vente]) as Jours,

    Nb

FROM

[data.xls]

(ooxml, embedded labels,table is [Page 1$]);

Temp2:

LOAD

    Mois,

    Jours,

    Sum(Nb) as sumNb

resident Temp

Group By Jours;

LOAD

    Jours,

    max(sumNb) as MaxNb,

    min(sumNb) as MinNb

resident Temp2

Group By Jours;

SET largeur= '=MaxNb-MinNb';

drop Table Temp;

drop Table Temp2;

[oknotsen]

I think I see a mistake in one of your statements. Try again after this change:

Temp2:

LOAD

    Mois,

    Jours,

    Sum(Nb) as sumNb

resident Temp

Group By Mois, Jours;

The sum does not work unless you put in a "group by" on all the fields you are not summarizing.