Create a day zero dimension in script

[johngouws]

Good day All, 

I am creating a Line Chart but need to create a 'day zero' dimension rather than show the date. I have tried something like the below statements, but the values work the wrong way around. If that makes sense. If you look at the image you see the accumalative values are correct but the values in the dimension should start 1 an go to 19. 

Date(Today(),'M/D/YY') - Date(Date,'M/D/YY') as DaysFromStart,
Date(Date,'M/D/YY') - Date(Today(),'M/D/YY') as DaysFromStart_2,

I appreciate your ideas on this. 

Thank you in advance. 

Stay safe..

[ashishkalia]

Hi @johngouws ,

My understanding is that you are calculating the days past and in that, you want to assign the first occurrence as one.

Case  Date	Today	DaysFromStart	DaysFromStart_New
05-Mar-20	24-Mar-20	19	1
06-Mar-20	24-Mar-20	18	2
07-Mar-20	24-Mar-20	17	3
08-Mar-20	24-Mar-20	16	4
09-Mar-20	24-Mar-20	15	5
10-Mar-20	24-Mar-20	14	6
11-Mar-20	24-Mar-20	13	7
12-Mar-20	24-Mar-20	12	8
13-Mar-20	24-Mar-20	11	9
14-Mar-20	24-Mar-20	10	10
15-Mar-20	24-Mar-20	9	11
16-Mar-20	24-Mar-20	8	12
17-Mar-20	24-Mar-20	7	13
18-Mar-20	24-Mar-20	6	14
19-Mar-20	24-Mar-20	5	15
20-Mar-20	24-Mar-20	4	16
21-Mar-20	24-Mar-20	3	17
22-Mar-20	24-Mar-20	2	18
23-Mar-20	24-Mar-20	1	19
24-Mar-20	24-Mar-20	0	20

Calculation for DaysStartFrom =  Today Date - Case Date. <This will give you the output as the one you are already having>

Now What you want is DaysStartFrom_New. To do so :
Step 1> Calculate the  MaxDaysPassed = Today() - Min(Case Date)  store this in a variable.

Step 2> Apply formula in the backend to create the fields as 
DaysStartFrom_New =  MaxDaysPassed - DaysStartFrom +1   

And you can use this DateStartFrom_New in your chart as a dimension.

Hope this helps.

Stay Safe!!
AK

[johngouws]

Hi @ashishkalia 

Thank you - that is what I am looking for. My numbers work out as negatives!. I am trying to follow your steps, this is what I am doing: 

Step 1> Create a variable > LET MaxDaysPassed = Today() - Min(Date);

Step 2> This is what my Load script looks like. I believe I am doing something wrong here. Please can you see where I am failing? 

Data:
LOAD
*,
$(MaxDaysPassed) - DaysFromStart +1 as DaysStartFrom_New
;
LOAD
Province as "Province/State",
"Country/Region",
Cases,
Status,
Date(Date,'M/D/YY') as Date,
Date(Today(),'M/D/YY') - Date(Date,'M/D/YY') as DaysFromStart,
Date_Num
FROM [$(v_RAW_QVD)\RAW\ZA_Data.qvd](qvd);

Stay safe - JG

[ashishkalia]

Hi @johngouws ,

I don't see any Issue in your script. If the issue is your numbers are coming -ve and you need it in positive then maybe you can use Fabs() function to get is corrected.

As far as the script is concern, I can't see this to result -ve outcome. May be, you check with the DaysFromStart, you created.
_ve should come only if you are [Date(Date,'M/D/YY')-Date(Today(),'M/D/YY')].

Regards!!
AK

[jonathandienst]

I think the problem may be the Let statement. You can't use Min here, it will return null, so the MaxDaysPassed variable is not defined. Add a TRACE and check the reload log to verify.

This means that the statement:

$(MaxDaysPassed) - DaysFromStart +1 

evaluates as 

- DaysFromStart +1 

which will be negative