topic Re: Merge Values from same Dimension - Last Week, This vs. Last Year in App Development

Merge Values from same Dimension - Last Week, This vs. Last Year

MacGyver — Fri, 24 Nov 2023 15:06:19 GMT

Hi Foks!

Hope to get an solution - i am still working on for 2 days 😞

I have a dimension "Invoicedate". It's formatted as date and autocalendar is working great.
Hint: We did not have an invoice on each day! Only monday to friday. So there is no value on a weekend.
Now i need to get this Dimension working for a Table Diagramm.

I want to get a table to compare our sales volume at last week against last week in previous year.

Sample Result:

So my problem is - if there is no Invoice on weekend - there is no value as an row in my dimension. My idea was to merge both dateranges from 2023 and 2022, to get a full list of days from 20.11. to 26.11. in order to calculate the sales volume on this day (of this year).

My last formular idea was this:

=date( aggr( only({1<[InvoiceDate.autoCalendar.Week]={"W$(=vWeek)"},[InvoiceDate.autoCalendar.Year]={"$(vActualYear)","$(vPreviousYear)"}>}[InvoiceDate]), [InvoiceDate] ),'DD.MM.')

I am so close... 😉

My Result with this formular:

As you can see - all Dates come together - by year. But i need them to combine to get the range from 13.11. (starting in '22) to 19.11. (Ending in '23). (*Dont be confused: This screenshot is a week before my first screenshot)

I hope anybody understands my thoughts and needs. Does anyone got an idea how to handle this?

Regards from Germany.

Mirco

Re: Merge Values from same Dimension - Last Week, This vs. Last Year

vincent_ardiet_ — Fri, 24 Nov 2023 15:19:32 GMT

If you have access to the loading script, this would be easier to add the missing days there I think.

Re: Merge Values from same Dimension - Last Week, This vs. Last Year

MacGyver — Mon, 27 Nov 2023 07:51:15 GMT

Mh, loading script is possible.

In which way would you do this? Creating a new Dimension - just a bunch of day in last 3 years !?

It there a way for a loop script to create this inline table?

Re: Merge Values from same Dimension - Last Week, This vs. Last Year

vincent_ardiet_ — Mon, 27 Nov 2023 08:39:30 GMT

If you have access to the script, you can add the missing dates for example.

Re: Merge Values from same Dimension - Last Week, This vs. Last Year

panosalexand — Mon, 27 Nov 2023 09:09:08 GMT

Hi Micro,

Please try this through the script and merge this Date within a master calendar.

Load *,

Date(Date#(InvoiceDate,'YYYY.MM'),'YYYYMMDD') as Date

From ......

Hope this is your request.

Regards,

Panos

Re: Merge Values from same Dimension - Last Week, This vs. Last Year

Aasir — Mon, 27 Nov 2023 09:42:54 GMT

=date(aggr(
only({1<[InvoiceDate.autoCalendar.Week]={"W$(=vWeek)"}, [InvoiceDate.autoCalendar.Year]={"$(vActualYear)","$(vPreviousYear)"}>} WeekStart([InvoiceDate])),
WeekStart([InvoiceDate])
), 'DD.MM.')

Re: Merge Values from same Dimension - Last Week, This vs. Last Year

marcus_sommer — Mon, 27 Nov 2023 11:18:24 GMT

The simples approach would be to add a date-field without a year-information within the calendar, like:

dual(day(Date) & '.' & month(Date), daynumberofyear(Date)) as DateWithoutYear

This is then the vertical dimension and your Year field becomes the horizontal dimension und sum(Sales) would work as expression. Instead of Year as horizontal dimension you may also use n expressions and define the years within a set analysis.

With more as two years included there should be no weekend-gaps but gaps in regard to holidays and/or other exceptions are further possible. If you really want to prevent any gaps you will need to populate the missing data within the fact-table.

Re: Merge Values from same Dimension - Last Week, This vs. Last Year

MacGyver — Thu, 30 Nov 2023 08:42:52 GMT

Thanks to all contributers an their solutions. It was hard to find a possible way.

The best solution to me is marked and inspired me to solve my problem.

Thanks all.