Second Top most for a varaible

[Aarya_Kankipati]

Second MOST Top 3 values in field

Year_Week	Product
202403	29
202402	56
202401	26
202352	58
202351	79
202350	47
202349	44
..	..
..	..
202201	39

I have a year week field as above; I created a variable (V_YW) equal to field (YW).

If I want to select 202402, it should give 202401, 202352, 202351 like past 3 weeks.

I tried this but it taking 202401, 202400, 202399
sum ({$ <YW={">=$(=max(YW)-3)"}>} product)

this it to get 202352, but its giving 202400

sum ({ <YW={'$(=max((V_YW)-2))'}>} product)

[Aarya_Kankipati]

Year_Week	Product
202403	29
202402	56
202401	26
202352	58
202351	79
202350	47
202349	44
..	..
..	..
202201	39

I have a year week field as above; I created a variable (V_YW) equal to field (YW).

If I want to select 202402, it should give 202401, 202352, 202351 like past 3 weeks.

I tried this but it taking 202401, 202400, 202399
sum ({$ <YW={">=$(=max(YW)-3)"}>} product)

this it to get 202352, but its giving 202400

sum ({ <YW={'$(=max((V_YW)-2))'}>} product)

[MK_QSL]

Create one more field like below.

DATA:

Load

Year_Week

    ,Product

    ,MakeWeekDate(Left(Year_Week,4), Right(Year_Week,2)) as Date

Inline

[

    Year_Week, Product

    202403, 29

    202402, 56

    202401, 26

    202353, 100

    202352, 58

    202351, 79

    202350, 47

    202349, 44

    202348, 50

    202347, 40

    202346, 45

];

and use expression as below

SUM({<Year_Week, Date = {">=$(=Date(Max(Date)-21))<$(=Date(Max(Date)))"}>}Product)

[marcus_sommer · Accepted solution]

You need a continuous week-field for this kind of task, for example created per autonumber(YearWeek) within the master-calendar or a field with a different math to yours - maybe done within a dual() like:

dual(Year * 100 + Week, Year * 52 + Week) as YearWeek

[Aarya_Kankipati]

I have the yearweek column in load editor as a field which runs and updates everyday; I also created a variable using that field. 

Name of Variable             Definition of Variable
V_YW                                             YW

I am looking for an expression to select past 3 weeks. (If I select this week, I need to get select the past 3 weeks, sum of products)

[marcus_sommer · Accepted solution]

With a dual-value like above suggested you could use your first approach, like:

sum ({$ <YW={">=$(=max(YW)-3)"}>} product)

and directly selecting the YW field.

To get it with variables would need more efforts because not only the field must follow the continuous logic else also the variable. But a variable doesn't provide a dual() feature which means the variable-value needs to be queried and depending of a possible year-switch transformed in different branches. Surely possible but it's not an elegant way.

Therefore field-selections would be easier whereby IMO it isn't too hard for an user to select 3 weeks (the field-sorting might be set to descending) or to apply a search like: >=202352. It's simple and fast and not more work as setting a variable-value.