in line chart x axis scale should always start from -24 irrespective of the data.

[Qlikbuddy]

I am deriving a field in the script called months_until_tld_expire and creating a line chart by count the subs in the measure.

as we know in qlik generally the scale values will come dynamically based on the data. but here user wanted this specific feature like always the scale should start from -24 irrespective of the data. why because their current excels report does the same. so wanted this feature.

scale limit is like -24 to +12 but starting point should always be the -24. if we select 202401 from discount_Start_month then it should start from -24 and ends at nov 2025 (month of today)

if we select 202402, then it should start from -24 and ends at oct 2025. etc.

kindly help how can we achieve this.

[Daniel_Castella]

Hi @Qlikbuddy 

If I'm not wrong, this cannot be done with the native bar chart. I mean, the offset cannot be moved from 0 to -24, at least in Qlik Sense, maybe in Qlikview it is possible.

However, there is a trick. You can create two measures, one as your expression and the second with -24 - your expression. Then, if you paint it white the segment for the first measure, it seems like you moved the offset. Like in this picture I made on my end:

The bad point is that you cannot colour each bar, all will be the same color.

Let me know if this helps you or if you need further details.

Kind Regards

Daniel

[Qlikbuddy]

Hi @Daniel_Castella , thanks for the response.

above excel screenshot i added to show that whatever the selection made it has to start from -24. but the real requirement is to get that in a line chart like below but scale should start from -24 always irrespective of the data/month that we selected. currently 202401 was selected and as per the data its correct scale is starting at -22 and but client specific requirement is like scale should always start from -24.

[Nagaraju_KCS]

Try this 

Go to the Properties panel for the Line Chart.

Navigate to Appearance.

Expand the X-axis settings.

Ensure Continuous scaling is selected.

Set the Range from Auto to Custom.

Minimum: Input the fixed value: -24

Maximum: Input the dynamic expression: =(Year(Today()) * 12 + Month(Today())) - (Year(Date#(Min(discount_Start_month), 'YYYYMM')) * 12 + Month(Date#(Min(discount_Start_month), 'YYYYMM')))

[marcus_sommer]

I think you could create such views by adding appropriate information to the data-model. In this case within the calendar. There could be a field like:

(year(today())*12+month(today()))-(year(MyDate*12+month(MyDate)) as PeriodOffset

which would be alone sufficient as UI dimension if the view should be working against a fixed period - today() might be replace with any other start-date.

If the view needs to be dynamic to a period-selection the above offset could be used within a condition like:

sum({< PeriodOffset = {">=$(=max(PeriodOffset)-24)<=$(=max(PeriodOffset)+12)"}>} MyValue)

which would filter the chart appropriate. If the dimension should be really show -24 to + 12 a similar logic might be applied to a calculated dimension, maybe something like this:

dual(PeriodOffset - $(=max(PeriodOffset), PeriodOffset)

Be aware that you will see only those periods which have appropriate fact-data because missing fact-records respectively NOTHING couldn't be shown even if there are such dimension-values within the calendar. In such cases is it usually the best to populate the missing data within the data-model.

[Qlikbuddy]

thanks for the response, but i dont see any such options

[Qlikbuddy]

thank you @marcus_sommer  for the response. 

I am using a teradata query in the back end and there is no master calendar for this actually. the dimension is derived like below.

,(EXTRACT(YEAR FROM TLD_Expiry_Date) - EXTRACT(YEAR FROM CURRENT_DATE)) * 12

+ (EXTRACT(MONTH FROM TLD_Expiry_Date) - EXTRACT(MONTH FROM CURRENT_DATE)) AS Months_Diff_Till_today

and measure is count({<TLD_Flag = {1}>} Distinct subs_id)

[Daniel_Castella]

Hi @Qlikbuddy 

In this case, I think that it would work better if done in the script, like this code:

TAB:
LOAD
A ,
B
FROM [lib://DataFiles/bar chart.xlsx]
(ooxml, no labels, table is Sheet1);

INL:
NoConcatenate
LOAD Month_Diff, Value
INLINE [
Month_Diff, Value
-24, 0
-23, 0
-22, 0
-21, 0
-20, 0
-19, 0
-18, 0
-17, 0
-16, 0
-15, 0
-14, 0
-13, 0
-12, 0
-11, 0
-10, 0
-9, 0
-8, 0
-7, 0
-6, 0
-5, 0
-4, 0
-3, 0
-2, 0
-1, 0
0, 0
1, 0
2, 0
3, 0
4, 0
5, 0
6, 0
7, 0
8, 0
9, 0
10, 0
11, 0
12, 0
];

Concatenate(TAB)
Load Month_Diff as A, Value as B
RESIDENT INL
where not exists (A,Month_Diff);

Drop table INL;

You simply create an Inline table with the values from -24 to 12 with 0 as value. Then you concatenate them to your data for the month diff that are not existing on your original data.

Then you end with a chart like this (note I used some random values for fields A and B coming from excel):

Obviously, you will have more fields than the ones I used for this simple example, but for me the idea should be this one. Let me know if it works for you.

Kind Regards

Daniel

[Nagaraju_KCS]

See this

[Qlikbuddy]

thanks again @Daniel_Castella, and this scale should change as per the tld binding filter. its like if the TLD binding is  24/24 then scale should start at -24 to 12.

if its TLD 12/12 then scale should start at -12 to 12

and if by fault we should show it like -36 to 12.