Announcements
cancel
Showing results for
Did you mean:
Creator III

## Last Year Sales Comparison Based On The Given Date

Hi Experts,

I have created the Current Year's and Last Year's sales reports separately. I need to show the last year's sales based on the given date.

I am maintaining the separate source file which is given below.

 Working Day CY DateKey LY DateKey 1 20230301 20220301 2 20230302 20220302 3 20230303 20220303 4 20230304 20220304 5 20230305 20220304 6 20230306 20220305 7 20230307 20220307 8 20230308 20220308 9 20230309 20220309 10 20230310 20220310 11 20230311 20220310 12 20230312 20220311 13 20230313 20220311 14 20230314 20220312 15 20230315 20220314 16 20230316 20220315 17 20230317 20220316 18 20230318 20220317 19 20230319 20220318 20 20230320 20220318 21 20230321 20220319 22 20230322 20220321 23 20230323 20220323 24 20230324 20220324 25 20230325 20220325 26 20230326 20220325 27 20230327 20220326 28 20230328 20220328 29 20230329 20220329 30 20230330 20220330 31 20230331 20220331

If we are in 20230310, we need to show  till 20220310 sales whereas we are in 20230313, we need to show till 20220311 sales. We need to show last year sales based on the given date table.

Sum({<Year = {\$(=max(Year)-1)},Date_Key = {"<=\$(=Date(AddYears(Date#(Max(Date_Key),'YYYYMMDD'), -1), 'YYYYMMDD'))"}>}Sales_Value)

I have Date table in my data model and sales table has a date key column.

Labels (5)

• ### SaaS

16 Replies

In general yes - but not with the "classical" association of data else you would need some extra logic, for example by using The As-Of Table - Qlik Community - 1466130. I doubt that's really expedient for your task else rather an unneeded detour - I think the extra condition of: Date_Key=p([LY DateKey]) is more causing problems instead of being helpful.

Creator III
Author

Could you please provide the expression

Maybe:

Sum({<Version_Desc = {'Actual','Adjustment'},ContinuousDay = {"<=\$(=max(ContinuousDay))"}, YearOffset = {"\$(=max(YearOffset )-1)"},Year,Month,Day_of_Week,Date_Key>}Sales_Value)/1000

Creator III
Author

Hi,

When I am using this expression,  that highlighted rows have to exclude when we select the 20230314

Like above hinted I suggest not to use the date_key as dimension else a dimension without the year-information. If there are further differences between last and current year it would mean that the match on the working-day isn't sufficient.

Creator III
Author

Even the text box is showing with 20220313 and 20220314 sales value.

I think it means that the created ContinuousDay didn't match your expectation. Just put the calendar-fields:

year, month, week, day, date, DD-MM, DayNumberofYear, ContinuousDay, YearOffset

in a table-box and filter various ContinuousDay values directly and/or in combination with other period-fields.

If you select 20230314 and afterwards the possible value of the ContinuousDay and de-selecting 20230314 again you should see at least two rows - at least one from the current year and at least one from the previous years - and all shown dates needs to fit to your expectation. If not you need to adjust the creating-logic within the calendar until it worked for all dates.