Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brunobertels
Master
Master

fonction P, Set analysis, compter des clients en N, présent en N -1

Bonjour

Sous Qlik Sense j'ai un tableau comptant par Année des Clients de 2010 à 2017

Je veux deux mesure : Nombre de Client en N et Nombre de Client en N présent en N-1

J'ai en dimension

Année  = Date.Autocalendar.Year

mesure : compter les clients

count(Client)

Mesure compter les clients en N présent en N-1 :

Ici j'essaye d'utiliser la fonction P et un set analysis mais voila 2 jours que je but dessus

je suis arrivé à cette mesure mais donne un erreur en 2016 :

=count({<Client = p({<[Date.autoCalendar.Year]={$(=max(year(Date))-1)}>} Client) >}Client)

Mais avec Cette mesure j'ai un fausse valeur en 2016

LE Max Année de mon tableau étant 2017

MA fonction P en gros dit que la dimension Année = Année de la Date Maximum de mes données soit 2017 - 1 = 2016

Ainsi en 2016 il m'indique le même nombre

Capture.PNG

Comment pallier a ce problème ?

J'aimais bien l'idée de la fonction P mais y a t il une autre approche ?

Merci d'avance

6 Replies
sunny_talwar

May be try this

Above(Count(Client))

or use

The As-Of Table

brunobertels
Master
Master
Author

Hi Sunny

Nice to take time for help.

Not sure Above and Or AsofTable is the solution , i will try it nethertheless

In fact i need to calculate for a year the number of client that was active the year before . So that i try to reach this requierment with the P function but without succes

In fact i a am able to calculate this with this mesure

=count({<Client = p({<[Date.autoCalendar.Year]={$(=max(year(Date))-1)}>} Client) >}Client)


it works well till 2016 due to my max year date in the data  who is 2017


See the previous table you can see that in 2016 i have the same result because my set analysis :

=count({<Client = p({<[Date.autoCalendar.Year]={$(=max(year(Date))-1)}>} Client) >}Client)

seems to said take the client of max year date -1 so 2017 - 1 = 2016


Then in 2016 i got the same number 7233


And i can't fix this issue


My sript is very simple


4 field aned an autocalendar dereived from field Date


[A$]:
LOAD
[N° Fact],
[Date],
[Client],
[TTC]
   

FROM [lib://brunobertels/factCHO_2010_2017.xls]
(biff, embedded labels, table is A$)


where [TTC]>0;


[autoCalendar]:
  DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
  Month($1) AS [Month] Tagged ('$month', '$cyclic'),
  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
  If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
  Year(Today())-Year($1) AS [YearsAgo] ,
  If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
  4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
  Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
  If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
  12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
  Month(Today())-Month($1) AS [MonthRelNo] ,
  If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
  (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
  Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [Date] USING [autoCalendar] ;


sunny_talwar

Oh, so you are checking count of clients which were present in year - 1 (2016) in your case. In that case, the numbers look right, isn't it? For 2016, all the clients should be counted because they were all present in 2016, right?

brunobertels
Master
Master
Author

Hi

In fact no , sorry for my bad English so that should have confused you

See the table below ,

I have selected year 2016 in filter :

Capture.PNG

The result is then OK

it's mean in 2016 there was 7233 active client and 3489 of them were active in 2015 too

So i want a straight table with year as dimension to get

number of client for each year and how many of them were present the previous year.

but with a some  years and without sélection ( here from 2015 till 2017)

2016 is bugging : Capture.PNG

I think it is because in my data a have record till 2017

and this part of the p function say max(year(Date))-1

So my Max Year Date is 2017 so max(year(Date)) - 1 = 2016

then in the strainght table in the line of 2016

Count client 2016 = 7233 and count client that was present in Max year Date - 1 is also 2016 and 7233 too ( in fact it shoould be 2015)

this is wrong and should be 3489

sfatoux72
Partner - Specialist
Partner - Specialist

‌Ta dimensio se nomme Date.Year et dans ton expression tu utilises Date.autocalendar.Year . Est-ce le même champs (renomm le label de la dimension ) ou des champs différents ?

brunobertels
Master
Master
Author

Bonjour Sébastien

Il s'agit bien de la même dimension. Je crois que je vais devoir ajouter un IF à ma formule pour contourner le problème.