dimension with day count from specific date date

[BlazkoG]

Hi I have a hard time with calculating a dimension with day count from specific date. I need it to use it later as as axis on chart.
So for example on covid-19 data, I've calculated a day 0 for every country dimension with:

=Aggr(min({<cases=-{'0'}>}date),countriesAndTerritories)

and try to count the days between day0 and date column (to mark day no. from day 0), and get this:

it counts only for the top date.

neither

 data-Aggr(min({<cases=-{'0'}>}date),countriesAndTerritories)

or,

Interval(date-Aggr(min({<cases=-{'0'}>}date),countriesAndTerritories),'D')

works. Both give same result. Why?

thx for any hint!

[Anil_Babu_Samineni]

First condition can print more data than second condition due to interval (). 

[BlazkoG]

Hi, thank you for the reply, but I dont understand.

Both formulas give same result. Only for max date, rest is null.

[Kushal_Chawda]

What is the expected output?

[BlazkoG]

Hi, column test (with date) minus the day 0. So below the 103 value, would follow 102, 101, 100 etc. I need this as a dimension, to further use it as a axis in a chart 

[Kushal_Chawda]

Both test and day0 are dimension or expressions?

[BlazkoG]

day0 is a calculated dimension, 'test' is a data loaded from dataset.

[Kushal_Chawda]

what is expression for day0?

[SerhanKaraer · Accepted solution]

Hi BlazkoG,

The reason you see only one record as output is aggregation of aggr function does not perfectly match to the the table use.

To allow your aggregation to repeat, you can use NODISTINCT in Aggr function.

 data-Aggr(NODISTINCT min({<cases=-{'0'}>}date),countriesAndTerritories)

I hope this solves your problem.

[BlazkoG]

Yes! Thank You! That solves it 🙂