nlonkai Member

sorry for my late answer. We had issues with our Domain. It worked out perfectly (and simple) with ETL Ranking and Window. Thanks

Many thanks for your help! I had to tweak a thing or two, but it worked out! Again, I appreciated your effort!

Hi @RGranada! Did you have the chance to take a look at my data source example? I am just wondering if this will work. Many thanks! Nikolaus

Hi, so I tried to create an example. Unfortunatley, I can´t give you real figures. The difference between my example source and the real source is that the real source is connected to our SQL server, it includes many more turnover columns and more IDs and Dates. But my example should illustrate which data we want to…

Hi, I tried your code, but unfortunately I have still the same result in terms of values without dates. I deleted Productgroups, because it was an additional field I created in my source. So that field is not necessary at this stage (I will include it on a later point within the ETL Flow). Regarding my grouping. I grouped…

Hi, I tried your sql code, but I included a grouping after your select a.Dates code. Enclosed you will find the result inserted into an excel. Basically, I have a lot of values without any linked date. Maybe I have to change something in the formula. Thanks Nikolaus

Hi, I had to add a previous step before applying your code. I had to create a grouping on my "Datum", so I don´t have more than one value per day, which caused an error within your code. If there is more than one sale per day your join will take the first sale of the day and multiply it by the quantity of sales for that…

Hi, I checked you approach and it nearly worked out. I had 2 issues: 1) For my first try I used a dataset with over 300k rows. I received a message which was something like:"Ups something went wrong". Afterwards I used a smaller one, which worked (about 6,5k rows). I guess that I received a time out or something 2) As you…

I tried to make an example. It is a bit difficult, because I use about 5 different sources directly linked to our SQL server. The example only shows the Total sales, in reality we have about 20 different sales figures, but I think that wouldn´t be necessary for the example. Furthermore, I grouped everything by Productgroup…

I change my first upload a bit.

Hi, unfortuanetly it doesn´t work again. I am not really sure what everything in the formula means, so would you like to see my data source in more detail (without actual sales of course)? Thanks Nikolaus

Hi, now I get an Invalid Formula : This calculation contained an error. SUM(`Total`) - SUM(CASE WHEN `Datum`= DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Total` END) / SUM(CASE WHEN `Datum` = DATE_SUB(`Datum`, interval…

Hi, I tried it: SUM(`Total`) - SUM(CASE WHEN `Datum`= DATE_SUB(`Date/Time Opened`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Total` END) / SUM(CASE WHEN `Datum` = DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN…

I am very sorry, I thought that a SQL case is necessary, I didn´t even think of such a convenient solution. Many thanks!!

Thats exactly it. Could you explain me how to aggregate the sales figures? Thanks nikolaus

No, my sort order is only based on "Date". Productgroup and Platform are only used as Filters.

Hi, I actually don´t need "Productgoup" and "Platform" within the table. I tried your suggestion before, but unfortunately I still had the same outcome, but without those 2 columns. Thanks Nikolaus