Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to use the dimension value in an expression (chart)

Hi all,

I have been trying to contruct a chart, which has a target value based on a trend from the previous year. It is used to see how much sales mtd in the current month there are supposed to be compared to the same month last year. It works fine, when you calculate one number, but when I put it in a graph, which is accumulating, I get the same value for each day in the month. I have been told to use the above and range funtions, but haven't been able to figure out how to implement it. Any help is highly appreciated.

Below is the expression, which needs to take the dimension value into account:

(

sum({$<[Date]={">=$(=(Floor(MonthStart(AddYears(Max( [Date]),-1)))))"}*{"<=$(=(Floor(AddYears(Max(Today()),-1))))"},[Calender MonthName]=,[Calender Year]=>}DB)

/

sum({$<[Date]={">=$(=(Floor(MonthStart(AddYears(Max( [Date]),-1)))))"}*{"<=$(=(Floor(MonthEnd(AddYears(Max(Today()),-1)))))"},[Calender MonthName]=,[Calender Year]=>}DB)

)

*

Sum({$<[Date]={">=$(=(Floor(MonthStart(Max( [Date])))))"}*{"<=$(=(Floor(MonthEnd(Max( [Date])))))"},[Calender MonthName]=,[Calender Year]=>} BudgetDB)

)

The first two lines calculate the trend from the month last year, the last is the budgeted sales for the current month. So on the e.g. the 10th in the current month you get a value on how much of your budget you should have accomplished. And I want to put this in an accumulating chart.

Thanks in advance!

1 Solution

Accepted Solutions
Not applicable
Author

I eventually solved it using RangeSum(Above(Sum(GP),0,31)))

View solution in original post

2 Replies
Not applicable
Author

I eventually solved it using RangeSum(Above(Sum(GP),0,31)))

tseebach
Luminary Alumni
Luminary Alumni

Actually i told you how to solve it