Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
samp1
Contributor II
Contributor II

AGGR expression with if condition is not working as expected

I have the below data, I am trying to get results as below, here I have 2 facts on Quarter and Month level, I need to show final results in table chart on a quarter level it should sum the fact 1 values on the quarter level if fact 1 does not exist it should take fact 2 for that month. here I am using the below expression.

Final Measure:

Sum(current) + if( Aggr(Sum(current), [Year-Month]) <= 0, Sum(future),0)

load * Inline [
Year-Qtr,Year-Month, current,future
2021-Q3, 2021-Jul, 30, 40
2021-Q3, 2021-Aug, 20, 50
2021-Q3, 2021-Sep, 10, 60
2021-Q4, 2021-Oct, 15, 20
2021-Q4, 2021-Nov, 25, 30
2021-Q4, 2021-Dec, 25, 50
2022-Q1, 2022-Jan, 10, 20
2022-Q1, 2022-Feb, 10, 20
2022-Q1, 2022-Mar, , 15
2022-Q2, 2022-Apr, , 20
2022-Q2, 2022-May, , 25
2022-Q2, 2022-Jun, , 30
2022-Q3, 2022-Jul, , 20
2022-Q3, 2022-Aug, , 50
2022-Q3, 2022-Sep, , 60
];

samp1_0-1647015083008.png

Here I should get 35 for 2022-Q1 since there is no fact1 value for 2022-Mar, so it should take the fact2 value as 15.

 10+10+15=35

thanks for your support.

 

Labels (3)
0 Replies