Help with using variables in an Inline load for use with a Pick function

[kdmarkee]

How can I create the chart below by using a dimension that takes the user’s chosen “As of Date” value (in this example the user choose an As of Date of 11/1/2020) and then show the first of the month for each month going back 3 months. (My real life scenario will go back 12 months, but using 3 for now in the example.)  So in my example, I would need plot 4 to actually be 11/1/2020, 3 to be 10/1/2020, 2 to be 9/1/2020 and 1 to be 8/1/2020.  “As of Date” is always a single value so it cannot be used as a dimension like a typical calendar field.  

To create the chart above I used the following in my script

DimDummy:
LOAD * Inline [
DimDummy
1
2
3
4
];

And in my chart object as an expression, I then can control each value for each dimension plot 1-4:

=Pick(DimDummy,
Num([3moback] / [Avg Daily Charges],'#,##0'),
Num([2moback] / [Avg Daily Charges],'#,##0'),
Num([1moback] / [Avg Daily Charges],'#,##0'),
Num([as of selected] / [Avg Daily Charges],'#,##0')
)

I cannot seem to make a DimDummy Inline table to reflect the dynamic dates I need.  I tried to establish variables (ie, AddMonths(AsOfDate, -2) would represent my plot number 3) and then use the variables in the inline load but it is not working (for starters, I cannot get past the comma in the variable giving me problems even if I try chr(44) in its place).

How would I do this or is there a better way to achieve my chart?  Thanks.

[QFabian]

Hi try this code :

DataX:
load * inline [
Date, Avg Daily Charges
01-10-2020, 80
30-09-2020, 63
29-09-2020, 128
28-09-2020, 146
27-09-2020, 70
26-09-2020, 106
25-09-2020, 123
24-09-2020, 90
23-09-2020, 60
22-09-2020, 136
21-09-2020, 57
20-09-2020, 79
19-09-2020, 80
18-09-2020, 94
17-09-2020, 82
16-09-2020, 100
15-09-2020, 80
14-09-2020, 54
13-09-2020, 58
12-09-2020, 56
11-09-2020, 142
10-09-2020, 82
09-09-2020, 61
08-09-2020, 104
07-09-2020, 61
06-09-2020, 66
05-09-2020, 101
04-09-2020, 123
03-09-2020, 125
02-09-2020, 99
01-09-2020, 135
31-08-2020, 84
30-08-2020, 61
29-08-2020, 113
28-08-2020, 97
27-08-2020, 69
26-08-2020, 141
25-08-2020, 77
24-08-2020, 97
23-08-2020, 53
22-08-2020, 60
21-08-2020, 142
20-08-2020, 55
19-08-2020, 65
18-08-2020, 89
17-08-2020, 149
16-08-2020, 68
15-08-2020, 86
14-08-2020, 64
13-08-2020, 131
12-08-2020, 56
11-08-2020, 78
10-08-2020, 94
09-08-2020, 85
08-08-2020, 75
07-08-2020, 67
06-08-2020, 87
05-08-2020, 86
04-08-2020, 114
03-08-2020, 103
02-08-2020, 70
01-08-2020, 85
31-07-2020, 117
30-07-2020, 110
29-07-2020, 117
28-07-2020, 80
27-07-2020, 51
26-07-2020, 79
25-07-2020, 97
24-07-2020, 87
23-07-2020, 91
22-07-2020, 70
21-07-2020, 79
20-07-2020, 129
19-07-2020, 123
18-07-2020, 102
17-07-2020, 74
16-07-2020, 88
15-07-2020, 94
14-07-2020, 66
13-07-2020, 147
12-07-2020, 53
11-07-2020, 110
10-07-2020, 135
09-07-2020, 144
08-07-2020, 125
07-07-2020, 112
06-07-2020, 77
05-07-2020, 108
04-07-2020, 61
03-07-2020, 113
02-07-2020, 86
01-07-2020, 57
30-06-2020, 142
29-06-2020, 109
28-06-2020, 102
27-06-2020, 62
26-06-2020, 117
25-06-2020, 84
24-06-2020, 110
23-06-2020, 132
22-06-2020, 144
21-06-2020, 92
20-06-2020, 53
19-06-2020, 51
18-06-2020, 100
17-06-2020, 55
16-06-2020, 134
15-06-2020, 127
14-06-2020, 143
13-06-2020, 115
12-06-2020, 77
11-06-2020, 72
10-06-2020, 88
09-06-2020, 68
08-06-2020, 115
07-06-2020, 85
06-06-2020, 99
05-06-2020, 63
04-06-2020, 127
03-06-2020, 78
02-06-2020, 141
01-06-2020, 138
31-05-2020, 64
30-05-2020, 140
29-05-2020, 113
28-05-2020, 59
27-05-2020, 126
26-05-2020, 120
25-05-2020, 89
24-05-2020, 118
23-05-2020, 66
22-05-2020, 89
21-05-2020, 120
20-05-2020, 149
19-05-2020, 92
18-05-2020, 55
17-05-2020, 85
16-05-2020, 139
15-05-2020, 102
14-05-2020, 130
13-05-2020, 87
12-05-2020, 82
11-05-2020, 147
10-05-2020, 76
];

Data:
load
num(MonthStart(AddMonths(Date,0))) as %_Date,
num(Date) as Date,
[Avg Daily Charges]
Resident DataX;

drop table DataX;

ADC:
Load
num(MonthStart(AddMonths(%_Date, 1))) as %_Date,
1 as Index,
[Avg Daily Charges] as ADC_1
Resident Data;

For vMonth = 2 to 4
left join(ADC)
Load
num(MonthStart(AddMonths(%_Date, $(vMonth)))) as %_Date,
1 as Index,
[Avg Daily Charges] as ADC_$(vMonth)
Resident Data
;
Next

an let me know if is what you were looking for

[kdmarkee · Accepted solution]

That won't work because the calculations are dynamic based on selections and therefore cannot be done in script, which is what I think you are suggesting.  I did actually find a rather simple solution by using an IF statement in a calculated dimension which basically builds my labels/plots so I can use my chart expression along with it... like so...

=Date(
if(DimDummy=1, AsOfDate,
if(DimDummy=2, AddMonths(AsOfDate, -1),
if(DimDummy=3, AddMonths(AsOfDate, -2),
if(DimDummy=4, AddMonths(AsOfDate, -3)
))))
)