Previous Fiscal Year data does not show up when i select date from calendar

[#1]

Hi all,

Wonder if you can help me.

I have a chart that calculates my sales of previous year using expression

Sum({$<Year_Fiscal_Order = {'$(=max(Year_Fiscal_Order)-1)'}>}[Sales Amount])

When i make a date selection from my calendar ([Invoice Date]) no values are showing for  previous year.

Can someone help please?

Product	Current Year	Previous Year
Milk	200	0
Bread	130	0
Tea	60	0
Coffee	80	0
Butter	30	0
Water	900	0
Fish	20	0
Meat	16	0
LOC	2	0

In my calendar load, I have created a separate table and left joined where it auto numbered all my fiscal year in ascending order and i left joined that to the main calendar. It works perfectly fine just that when i select dates, then it goes off.

Regards,

[sunny_talwar]

May be like this:

Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order)-1)}, [Invoice Date]>}[Sales Amount])

[#3]

Sunny that does make my expression to respond, but i am expecting the expression to only return values for the dates i have selected but for previous fiscal year. this is ignoring my dates selections completely and just showing me previous year selections

[sunny_talwar]

I think it would be better if you can use Date instead of Year field to get this working. If possible, can you share a qvw file where you are trying this out?

[#5]

Hi Sunny,

It will be hard to share customer data but i have created a app and replicated my issue.

Basically the expressions are calculating the totals with set Analysis based on Fiscal year but when i select a default calendar year the previous year stops working.

Cant think of a way to solve this problem.

Regards,

Sarb

[sunny_talwar]

All you need to do is ignore any date related field where you plan to make a selection. For instance, If you think you will be making selections in Year_Fiscal and MonthYear field, then try this:

Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order)-1)}, Month = {"=Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order))}>}[Amount]) > 0"}, Year_Fiscal, MonthYear>}[Amount])

The straight table below is using the new expression and that is why it is able to show 78 for previous column.

You can continue to add all Date related field like this

Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order)-1)}, Month = {"=Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order))}>}[Amount]) > 0"}, Year_Fiscal, MonthYear, Year_Calendar, Month_Order, NumMonth, NumMonthYear>}[Amount])

[#7]

Hi Sunny,

Thanks for reply.

I can see that the expression is ignoring the selections of my date but what i wanted was if no dates are selected then it should respond to fiscal year calculation, but a user selects date, then it should look for amount in previous year for the selected dates.

At the moment, my expression is only locked to previous fiscal year hence it is only responding to that and when i select dates and my previous year doesnt show anything.

Is there a way i can tweek the expression to show fiscal year by default and if a user selects dates then it should compare with dates of previous year?

Regards,

(Attached is the same which shows what i described above.)

[vishsaggi]

Hello Sunny the one highlighted in red below can you confirm ?

Is that used to get the Amount for Actual ?

Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order)-1)}, Month = {"=Sum({$<Year_Fiscal_Order = {$(=Max(Year_Fiscal_Order))}>}[Amount]) > 0"}, Year_Fiscal, MonthYear>}[Amount])

[sunny_talwar]

Yes, that is what it is there for

[sunny_talwar]

Your chart is on Month level and you are looking to see Date comparison when a single date is selected? I think I am not 100% sure of what exactly are you looking to do. May be provide us the expected output in an Excel file based on different selections