Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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] ;
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?
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 :
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 :
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
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 ?
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.