Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
kdmarkee
Specialist
Specialist

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

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.  

asofdate.GIF

 

 

 

 

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.

Labels (3)
1 Solution

Accepted Solutions
kdmarkee
Specialist
Specialist
Author

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

View solution in original post

2 Replies
QFabian
Specialist II
Specialist II

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

 

QFabian
kdmarkee
Specialist
Specialist
Author

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

View solution in original post