Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data set which keeps changing everyday. But, the data set does not have date field when it is changed.
Example Data set:
Last week (week 45) , data looks like this:
Data1:
Region | Product | Sale | Cost |
Asia | Apples | 100 | 50 |
Asia | Oranges | 100 | 100 |
Asia | Mangoes | 200 | 50 |
USA | Apples | 100 | 100 |
USA | Oranges | 200 | 50 |
USA | Mangoes | 200 | 100 |
Europe | Apples | 200 | 50 |
Europe | Oranges | 100 | 100 |
Europe | Mangoes | 200 | 50 |
Europe | Bananas | 300 | 50 |
Script:
Load
Region,
Product,
Sale,
Cost,
(Sale-Cost) as Profit
From Data1:
Result Table:
It will be a straight table with dimensions as region and measure as Sum(profit).
Region | Profit |
Asia | 200 |
Europe | 550 |
USA | 250 |
But all the above results are for week 45. Now for week (46), my data may look like this:
Region | Product | Sale | Cost |
Asia | Apples | 100 | 100 |
Asia | Oranges | 150 | 200 |
Asia | Mangoes | 200 | 100 |
USA | Apples | 250 | 200 |
USA | Oranges | 300 | 100 |
USA | Mangoes | 350 | 500 |
Europe | Apples | 400 | 100 |
Europe | Oranges | 450 | 200 |
Europe | Mangoes | 300 | 100 |
Europe | Bananas | 300 | 100 |
I would like my result table to look the same way as above but time i would like to see my old results for week 45 as well:
Region | Week | Profit |
Asia | 46 | 50 |
Europe | 46 | 950 |
USA | 46 | 100 |
Asia | 45 | 200 |
Europe | 45 | 550 |
USA | 45 | 250 |
How do i achieve this?
Remember, the week number is unknown. I would that to be a calculated field based on the run date of the script.
Can someone help me how to do this?
Thanks,
Vidya
Hi,
you can use Today() and Week() function to calculate week.
Load Week(Today()) as Week, //shows just week number, like 45 WeekName(Today()) as WeekName //shows year as well: 2018-45 * From SourceTable;
Be carefull with first week of year, it can get tricky (but there's a setting for that). See Broken Weeks.