Qlik sense how to look for a first time appeared value in the year by condition

[Vince_CH]

Hi All,

from a list like below, material numbers could be repeated over the years. i want to pick out the new field value first time appeared in this year, say, 2018. i tried with expression like: if(Min(year)='2018', count(DISTINCT ([Material number])), but it doesn't work out, who can give some advices? thanks. 

[tresesco]

What would be your expected output from the sample given?

[Anil_Babu_Samineni]

Try this?

if(year = Max(TOTAL year),  count(DISTINCT ([Material number]))

Or

Count({<year = {$(=Max(year))}>} DISTINCT ([Material number])

[Vince_CH]

Hi Tresesco, i intend to count the brand new materials first time appeared in this year, in order to calculate the percentage of new material portion in the whole materials received.

[Vince_CH]

---

@Anil_Babu_Samineni wrote:

Try this?

if(year = Max(TOTAL year),  count(DISTINCT ([Material number]))

Or

Count({<year = {$(=Max(year))}>} DISTINCT ([Material number])

---

Hi, the logic of above expression seems to be opposite to what i expected ,as it is intending to count the current year material number DISTINCT quantity, however, the same materials could be received also in past years, instead of being new materials here.

[christophebrault]

Hi,

In your case, I think you can use Indirect set analysis like this formula :

Sum({<Material=e({<Year={$(=Max(Year)-1)}>})>} Qty)

Use it in you table for Qty, and select a Year. It will excluse all Material with Qty for the previous year.

For several years of historic, you can use :

Sum({<Material=e({<Year={"<=$(=Max(Year)-1)"}>})>} Qty)

[Vince_CH]

---

@christophebrault wrote:

Hi,

In your case, I think you can use Indirect set analysis like this formula :

......

For several years of historic, you can use :

Sum({<Material=e({<Year={"<=$(=Max(Year)-1)"}>})>} Qty)

---

Hi Christophebraul, thanks for reply.

i intend to count DISTINCT new material numbers quantity starting to appear in this year. So the expression formula is supposed to be Count(DISTINCT{<?=e({<Year={"<=$(=Max(Year)-1)"}>})>} [Material Number]), correct? what should be on the position of question mark "?" instead?

[Vince_CH]

another question is, as i use the expression in KPI expression on sub headlines, with new materials counts of YTD, also with a counterpart of LYTD, then how to write the expression text for same of LYTD?? thanks!

[christophebrault]

Then, put 

Count(DISTINCT{<[Material Number]=e({<Year={"<=$(=Max(Year)-1)"}>})>} [Material Number])

For previous year :

Count(DISTINCT{<[Material Number]=e({<Year={"<=$(=Max(Year)-2)>=$(=Max(Year))"}>})>} [Material Number])

[Vince_CH]

Hello Christophebraul, the first expression works great, thanks.

however, the second one for previous year is not matched, the calculated results is higher than my excel calculation. Also if in terms of LYTD by ending point at Dec.1st, which means for previous year, the formula shall calculate till same date, so how to write the latter expression differently?

thank you and have a nice weekend!