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.

[marcus_sommer]

IMO the use of (n) master-calendars is absolutely mandatory - (nearly) each kind of data-set has a date-relation and beside the simple deriving of period-information like year, month and so on all kinds of flags, offset, rolling-periods and and and could be included ... Not only useful within the data-models else within the ETL part to join/map any information.

Very important: it's enough to do this work once for the entire environment - and then picking from there the specific needed fields and periods. Therefore don't waste time to do all the date-related preparation/calculation again and again and in the worst case even within the UI.

How to use - Master-Calendar and Date-Values - Qlik Community - 1495741

[Daniel_Castella]

Hi @Qlikbuddy 

Well, I would need to have more information about how you create the filter, but I think that if you adjust the dimension like in this picture, you can cut to the wanted value. 

For the faulty, if the maximum range needs to be -36, you need to expand the inline table I provided in the script from -24 to -36; and later cut the graph like in the picture above. Note that the value can be set through a variable which value can change based on the filter you commented.

Kind Regards

Daniel

[Qlikbuddy]

thanks much @marcus_sommer for sharing your incredible article link. i respect your suggestion for using the master calendar. but here my case is little different with too many date columns. its about discounts related dashboard and there are 3 different types of discounts and each with start, end and expire dates. and main table is Base table which is having another date column. 

I have created a master calendar by using the link table approach where I used base table date + and all 3 discounts expiration dates to created a Common_Date. 

I kind of never worked with these many dates (3 discount start, 3 discount end, 3 discount expiration and 1 Base closing date) almost 10 dates. and its really messy. can you please show some light on how to combine the dates when we have these many. 

currently for 3 discount expiration + 1 Base closing date --- I have created a common_date. 

can I do the same for 3 discount start dates as well ?

[marcus_sommer]

Having 10 date-fields within the source-data doesn't mean that you need 10 date-fields within the final data-model. Quite probably could the number of fields be reduced - depending of the relationship between the date-fields and the view-requirements you might be ending with a single date-field.

Unfortunately there is no simple rule how to merge them and simplifying the data-model - ideally is a star-scheme with a single fact-table and n surrounding dimension-tables but sometimes it might be sensible not be to restrictive - and maybe keeping some aggregated facts as a dimension-table, too.

If your start/end dates are date-areas it's within the most scenarios helpful to resolve them into dedicated dates with an intervalmatch-logic (or more powerful + flexible with internal load while-loops). Here is the idea beautifully explained: IntervalMatch - Qlik Community - 1464547

Further I think that the 3 different discount-types could be resolved from a horizontal level (= n fields) to a vertical level (= n rows) which means there is then only one date-field + an extra field containing the information which discount-type belonged to it. Such kind of things might be done per: The Crosstable Load - Qlik Community - 1468083.

I would also include all kind of offset-calculations + flagging-stuff (expired, renewed, whatever ...) in these steps.

All these things should be carefully considered before going to use n master-calendar and/or combining them in a canonical way: Canonical Date - Qlik Community - 1463578 because the above provides a native usability while using the n calendars is more complex.