Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
suryajegan
Creator
Creator

Privious Year YTD

Hi

I am struggling alot to show the Previous year YTD info in a table.

Below mentioned is my expression. Its showing the entire previous year data. But logically it has to show till August 2016.

Sum({<Year={$(vPriorYear)}, fiscal_quarter=, Month=, [Date]=, Datekey={">=$(=Num(YearStart(AddYears(Max(Datekey),-1)))) <=$(=Num(AddYears(Max(Datekey),-1)))"}>}accessories_revenue)



Can you please help me to fix this issue?

1 Solution

Accepted Solutions
suryajegan
Creator
Creator
Author

I used the below expression...Its working as per my expectation .... tanq so much for all  your suggestions

=Sum({<segment=,Year={$(vPriorYear)}, fiscal_quarter=, Month=,Date={"$(='>='&Date(YearStart(Max(Date),-1),'MM/DD/YYYY') & '<=' & Date(AddYears(Max(Date),-1),'MM/DD/YYYY'))"}>}accessories_revenue)

View solution in original post

10 Replies
sibin_jacob
Creator III
Creator III

Create a flag for Previous Year and current year

Then use that flag in the expression

if(Month(Date_cloumn)>8,year(Date_column)&' to '&year(Date_column)+1,

if(Month(Date_cloumn)<8,year(Date_column)-1&' to '&year(Date_column),

if(Month(Date_cloumn)=8 and Date(Date_column,'DD')<=25,

year(Date_column)-1&' to '&year(Date_column),

year(Date_column)&' to '&year(Date_column)+1

))) as Fiscal_Year

suryajegan
Creator
Creator
Author

Hi Sibin

Tan so much for your reply...

I already have a previous year and current year flag....

Let vlastyear=YearStart(Today(),-1);

Let vcurrentyear=YearStart(Today());

But nothing is working out

Do you have any straight forward formula which gives Prior year YTD output?

sibin_jacob
Creator III
Creator III

Try this

Create Month Column, values should be 1,2,3 etc.

if it already available, you can use the existing column or you can create using

Date(Date_Column,'MM') as Month_Column

Create variable vMonth_value

Definition give it as 8

Sum({<Year={$(vPriorYear)}, fiscal_quarter=, Month=, [Date]=,

Month_Column={"<=$(=vMonth_value)"},

Datekey={">=$(=Num(YearStart(AddYears(Max(Datekey),-1)))) <=$(=Num(AddYears(Max(Datekey),-1)))"}>}accessories_revenue)

yenumulagopi
Contributor III
Contributor III

Hi Surya,

Can you please try with the below expression

Sum({<Year={$(vPriorYear)}, fiscal_quarter=, Month=, [Date]=, Month={'<=$(=max({<Year={$(=max(Year))}>}accessories_revenue)

If it is not working, please let me know. In this expression instead of using the variable you can use

Year={$(=max(Year)-1)}.


Thanks

Gopi

suryajegan
Creator
Creator
Author

Hi Gopi

Tanq for you reply...

I think there is some issue in the below part

Month={'<=$(=max({<Year={$(=max(Year))}>}


Can you please chk?

suryajegan
Creator
Creator
Author

I have reformed my formula like this

Sum({<Year={$(=max(Year)-1)},fiscal_quarter=, Month=, [Date]=, Month={'<=$(=max({<Year={$(=max(Year))}>}))>'}>}accessories_revenue)

Its giving null output

thannila
Creator
Creator

Try this below code:

sum({<Year={$(vPriorYear)},{<Datekey={">=$(=YearStart(AddYears(max(Datekey),-1)))<=$(=AddYears(max(Datekey),-1))"},

Month=,Quarter=,WeekDay=,MonthlyWeekNumber=>}accessories_revenue)


@

PrashantSangle

Try below

Sum({<Year={$(=max(Year)-1)},fiscal_quarter=, Month=, [Date]=,Month={"<=$(=max(Month))"}>}accessories_revenue)

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
suryajegan
Creator
Creator
Author

I used the below expression...Its working as per my expectation .... tanq so much for all  your suggestions

=Sum({<segment=,Year={$(vPriorYear)}, fiscal_quarter=, Month=,Date={"$(='>='&Date(YearStart(Max(Date),-1),'MM/DD/YYYY') & '<=' & Date(AddYears(Max(Date),-1),'MM/DD/YYYY'))"}>}accessories_revenue)

View solution in original post