topic Re: Graph to extend formula to all months in QlikView

Graph to extend formula to all months

anagharao — Tue, 07 Apr 2020 11:58:47 GMT

Hi Everyone

I have data in the format :

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?

lironbaram — Tue, 07 Apr 2020 13:02:53 GMT

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 — Tue, 07 Apr 2020 13:08:34 GMT

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