Set expression for the below data set

[charu16aug]

Year, Month ,Sponsored MAil, YTD Mail

2020,Jan,      0,         0   

2020,Feb,      0,         0 

2020,March,      809,         809    

2020,April, 0, 809

2020, May,0,809

2020,Jun,290,1099(i.e 809+290)

....

...

2021,Jan,      0,         0   

2021,Feb,      200,       200 

2021,March,      200,         400

2021,April, 0,               400

2021, May,0,           400

2021,Jun,0  ,400

and so on...................

So, here YTD is calculated using Sponsored Mail, if the value is 0 for Sponsored Mail and year is same.ex-2020 ,then the YTD should also be 0, but if there comes some value greater than 0 for Sponsored Mail then YTD will Add that value to 0, and again if for the same year and different month the value for Sponsored Mail is 0 then the YTD will remain that value but the year should also be taken into consideration.

please provide an set expression for this pattern?

[RafaelBarrios]

Hi @charu16aug 

i would do it in script

Try this

first, to be sure data is sorted correctly, create a date field in your loading table

first_Load:
LOAD 
date#(Year&'/'&left(Month,3),'YYYY/MMM') as datefield,
* 
inline [
Year, Month ,Sponsored MAil, YTD Mail
2020,Jan,0,0 
2020,Feb,0,0 
2020,March,809,809 
2020,April,0,809
2020, May,0,809
2020,Jun,290,1099
2021,Jan,0,0 
2021,Feb,200,200 
2021,March,200,400
2021,April,0,400
2021, May,0,400
2021,Jun,0,400
];

now, sort by the new date field and ask for the previous record to check if it is in the same year or not

if so, use current  [Sponsored MAil] plus the previous YTD field

second_Load:
load 
if(Year <> previous(Year),
[Sponsored MAil],
[Sponsored MAil]+peek(YTD_mail_new)) as YTD_mail_new,
*
resident first_Load
order by datefield asc;

Drop the previous table

drop table first_Load;

hope it helps

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!

[charu16aug]

Thanks @RafaelBarrios ,  but i want to calculate this YTD_mal using set expression,it will be really helpful if you provide a set expression for that

[RafaelBarrios]

i see.

try this 

same data, just be sure Qlik understand Year and month as date fields

first_Load:
LOAD 
date#(Year,'YYYY') as Year,
date#(left(Month,3),'MMM') as Month,
[Sponsored MAil]
inline [
Year, Month ,Sponsored MAil, YTD Mail
2020,Jan,0,0 
2020,Feb,0,0 
2020,March,809,809 
2020,April,0,809
2020, May,0,809
2020,Jun,290,1099
2021,Jan,0,0 
2021,Feb,200,200 
2021,March,200,400
2021,April,0,400
2021, May,0,400
2021,Jun,0,400
];

now, this can change according to chat type

Pivot Table
Dim1: Year
Dim2: Month
MEasure: RangeSum(Before(sum([Sponsored MAil]), 0, ColumnNo()))

Standard Table
Dim1: Year
Dim2: Month
MEasure: RangeSum(above(sum([Sponsored MAil]), 0, 6)) //i only have 6 months for each year

Standard Table (using qlik options)
Dim1: Year
Dim2: Month
MEasure: sum([Sponsored MAil])

hope it helps

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!