Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anagharao
Creator II
Creator II

Graph to extend formula to all months

Hi Everyone

I have data in the format :

AccountDepartmentVendorDateActual
Acc1Dept1Vend1Jan-20191200
Acc1Dept2Vend1Jan-20191748
Acc2Dep2Vend2Feb-2019122
...............

 

I would like to get a graph with the below measures and Date [MMM-YY] as the dimension

  • Cost (Jan, Feb...) : Actuals that come in
  • Avg Annld Cost : for the rest of the months in the year
    (Sum of all months available) + (Latest month Cost* (12 - number of months passed))
    Example: IF the latest month is Feb
    (Jan + Feb) + (Feb *(12 - 2))

  Any suggestions for the second formula?

 

 
 

 

2 Replies
lironbaram
Partner
Partner

hi 

you'll need to load the months into your date field and then it's wont be that complicated 

you'll expression will look like this : 

if(Sum(Actual)=0,
(sum(total Actual)+sum({<Date={"$(=max(Date))"}>}Actual)*(12-num(month(max(Date)))))/12,Sum(Actual))

anagharao
Creator II
Creator II
Author

Thank you for the reply!

I did try TOTAL and Set analysis to fetch the latest date. There are two issues with that:

- Since the chart is dimensioned by Month-YY the set analysis for the current month cant be extended to all future months

- The total doesnt work when i make selections in any dimensions