Announcements
cancel
Showing results for
Did you mean:
Partner - Specialist

## Create a day zero dimension in script

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.

Stay safe..

4 Replies
Partner - Creator

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 DaysStartFromToday 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

Partner - Specialist
Author

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:
*,
\$(MaxDaysPassed) - DaysFromStart +1 as DaysStartFrom_New
;
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

Partner - Creator

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

Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein