topic Re: Last Year Sales Comparison Based On The Given Date in App Development

Last Year Sales Comparison Based On The Given Date

dineshm030 — Fri, 15 Nov 2024 21:56:00 GMT

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.

@sunny_talwar @MayilVahanan @marcus_sommer

Thanks in advance.

Re: Last Year Sales Comparison Based On The Given Date

panosalexand — Tue, 04 Apr 2023 07:36:59 GMT

Hi,

set a variable MaxYear=max(Year). Then go to the Expressions Tab and in definition write =Sum(Year=MaxYear,Sales_Value). Afterwards write one more expression =Sum(Year=MaxYear-1,Sales_Value).

In this way you will have the comparison between the years. If you prefer this analysis to compare months or Quarters CY with LY then you have to have connect your table with a calendar.

Thanks

Re: Last Year Sales Comparison Based On The Given Date

MayilVahanan — Tue, 04 Apr 2023 07:40:36 GMT

What is the link between Date tables and workingday, CY DateKey & LY DateKey info?

Re: Last Year Sales Comparison Based On The Given Date

marcus_sommer — Tue, 04 Apr 2023 07:53:37 GMT

The mistake is the use of the date_key as dimension because only one date belonged to it - and not the one from the previous year or any other date. Principally it's possible to force any calculation to each dimension-value but it's difficult, slow and has a lot of disadvantages - it's very seldom expedient.

Better would be you removes the year-information from the dimension - just using MM-DD which comes from your master-calendar and is linked to your date_key. Also useful would be to create all kinds of flags of YTD and MTD of any years within the calendar and/or if they don't mandatory relate to today() else to selected values with the UI simplifying logic like:

daynumberofyear(date) as ContinuousDay,
year(date) - year(today()) as YearOffset

and the the expressions may look like:

sum({< ContinuousDay = {"<=$(=max(ContinuousDay))"}, YearOffset = {"$(=max(YearOffset ))"}>} Value)
sum({< ContinuousDay = {"<=$(=max(ContinuousDay))"}, YearOffset = {"$(=max(YearOffset )-1)"}>} Value)

Re: Last Year Sales Comparison Based On The Given Date

dineshm030 — Tue, 04 Apr 2023 11:50:22 GMT

Hi,

The CY Datekey is the key of the Dates table and this excel files.

Re: Last Year Sales Comparison Based On The Given Date

dineshm030 — Tue, 04 Apr 2023 12:33:29 GMT

Hi Marcus,

I have joined the master calendar and this Excel by Date_Key. I have created the two fields which are you mentioned. The values are not populating for LY.

Kindly help me out with this task.

I have used the below expression:

Sum({<ContinuousDay = {"<=$(=max(ContinuousDay))"}, YearOffset = {"$(=max(YearOffset ))"}>}Sales_Value)

Sum({<,ContinuousDay = {"<=$(=max(ContinuousDay))"}, YearOffset = {"$(=max(YearOffset )-1)"}>}Sales_Value)

Thanks in advance @marcus_sommer @MayilVahanan @sunny_talwar

Re: Last Year Sales Comparison Based On The Given Date

marcus_sommer — Tue, 04 Apr 2023 13:01:48 GMT

If you have not only a single selection on the day else various other ones to the period-information you will need to address them, too within the condition - means ignoring them, maybe like:

Sum({< ContinuousDay = {"<=$(=max(ContinuousDay))"},
YearOffset = {"$(=max(YearOffset )-1)"},
Year, Month >}Sales_Value)

Beside this there is a small syntax issue in your expression - a leading comma before ContinuousDay - probably a copy & paste mistake.

Re: Last Year Sales Comparison Based On The Given Date

dineshm030 — Wed, 05 Apr 2023 02:07:23 GMT

Hi Marcus,

If i select Date_Key 20230314, LY sales should be adding upto 20220312 but it is added upto 20220314. Same if we select 20230313, LY sales should be adding upto 20220311.

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

Based on the CY DateKey selection, LY sales should be adding upto the given LY DateKey.

Thanks in advance @marcus_sommer @MayilVahanan @sunny_talwar

Re: Last Year Sales Comparison Based On The Given Date

marcus_sommer — Wed, 05 Apr 2023 07:05:14 GMT

Like above hinted I wouldn't try to use multiple date-keys - neither for this task nor another one - else just one key which is the link between the fact-table and the master-calendar.

If you don't want to refer the YTD logic to calendar-days else to working-days you could remain by the above suggested main-logic just by adjusting the creation of the ContinuousDay. This might be done by using an appropriate offset-value - if I remember it correctly it's just +- 1 between the years - or by creating a working-day flag of 0/1 (in regard to weekends, holidays and so on) which are within a following step accumulated (for example by using interrecord-functions like previous() and peek() within a preceding-load of the calendar).

Re: Last Year Sales Comparison Based On The Given Date

dineshm030 — Wed, 05 Apr 2023 08:37:03 GMT

Hi Marcus,

Is it possible to bring the LY DateKey from 20220301 to 20220312 when I select 20230314 from Date_Key.

I am using the below expression with possible of LY DateKey.

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

Thanks in advance @marcus_sommer @MayilVahanan @sunny_talwar

Re: Last Year Sales Comparison Based On The Given Date

marcus_sommer — Wed, 05 Apr 2023 08:50:42 GMT

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.

Re: Last Year Sales Comparison Based On The Given Date

dineshm030 — Wed, 05 Apr 2023 09:41:06 GMT

Could you please provide the expression

Re: Last Year Sales Comparison Based On The Given Date

marcus_sommer — Wed, 05 Apr 2023 09:44:18 GMT

Maybe:

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

Re: Last Year Sales Comparison Based On The Given Date

dineshm030 — Wed, 05 Apr 2023 09:56:24 GMT

Hi,

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

Re: Last Year Sales Comparison Based On The Given Date

marcus_sommer — Wed, 05 Apr 2023 11:09:40 GMT

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.

Re: Last Year Sales Comparison Based On The Given Date

dineshm030 — Wed, 05 Apr 2023 11:48:07 GMT

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

Re: Last Year Sales Comparison Based On The Given Date

marcus_sommer — Wed, 05 Apr 2023 12:08:03 GMT

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.