Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
jkneuf
Contributor
Contributor

How to create a trendline based on subset of data?

My customer, an inventory manager, has asked for a trendline over a specific time frame while also displaying the complete dataset. For example, on 1-Mar-2022 the customer implemented a program to focus on trying to improve the on-hand inventory for specific items. The code below limits the trendline (purple) for dates only >= to March 1st, but it has the same slope/intercept as the entire dataset (light blue). The slope should be steeper with a different intercept. How do I subset the data to only use the subset that has date >= March 1st? 

 

if(date >= MakeDate(2022,03,01),

linest_m(total aggr(if(100*sum(onhand)/sum(authorized),100*sum(onhand)/sum(authorized)),date),date)
* only({1}date)

+ linest_b(total aggr(if(100*sum(onhand)/sum(authorized),100*sum(onhand)/sum(authorized)),date),date)
)

Labels (1)
1 Reply
Chasityberg
Contributor
Contributor

it is working for text object respective of selection.but in table chart i have four aggregation fields based on moth-year dimension and only for one aggregation i have to show previous 3 months excluding current month so i am using above set expression for that but it is not working.it is giving month wise aggregation instead of previous 3 months results.how can i achieve this in table chart with dimension.                   Panorama Login Charter