Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
johngouws
Partner - Specialist
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. 

Capture.PNG

Thank you in advance. 

Stay safe..

 

 

4 Replies
ashishkalia
Partner - Creator
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  DateTodayDaysFromStartDaysFromStart_New
05-Mar-2024-Mar-20191
06-Mar-2024-Mar-20182
07-Mar-2024-Mar-20173
08-Mar-2024-Mar-20164
09-Mar-2024-Mar-20155
10-Mar-2024-Mar-20146
11-Mar-2024-Mar-20137
12-Mar-2024-Mar-20128
13-Mar-2024-Mar-20119
14-Mar-2024-Mar-201010
15-Mar-2024-Mar-20911
16-Mar-2024-Mar-20812
17-Mar-2024-Mar-20713
18-Mar-2024-Mar-20614
19-Mar-2024-Mar-20515
20-Mar-2024-Mar-20416
21-Mar-2024-Mar-20317
22-Mar-2024-Mar-20218
23-Mar-2024-Mar-20119
24-Mar-2024-Mar-20020

 

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

johngouws
Partner - Specialist
Partner - Specialist
Author

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);

Capture.PNG

Stay safe - JG

 

ashishkalia
Partner - Creator
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

jonathandienst
Partner - Champion III
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