Sumif using calculated columns for if conditions

[MDH1]

I'm pretty new to Qlikview, and having searched around the discussion boards for a few days and trying various approaches am still hitting a brick wall, so hoping somebody can help me out.

I've mocked up in Excel what I'm trying to get Qlikview to do - it's the RHS table I'm trying to produce, with the Total Initial Balance column being the one I'm struggling with. I don't seem to be able to do a lookup on the Balance column in table 1 based on the date ranges in each row of table 2.

i.e. If I put in specific dates, the below expression does what I'd expect (in the chart table object in the .qvw attached) for the Total_Initial_Balance column.

=sum(Total if('01/01/2019'<= JoinedMonth and JoinedMonth <= '01/01/2020' and first_entry=1, Balance)) 

But if I try and use the Min and Max dates in each row instead of constant dates, it just returns zeroes.

=sum(Total if(Min_JoinedMnth<= JoinedMonth and JoinedMonth <= Max_JoinedMnth and first_entry=1, Balance)) 

I've tried various approaches with set expressions etc, but no luck as yet.

If anybody has any ideas I'd be grateful.

[lironbaram · Accepted solution]

Hi , 
your script should look something like this 

Table1:
LOAD Customer_ID, 
     JoinedMonth, 
     Months_since_joined, 
     Balance, 
     [Paid Amount], 
     [first entry]
  FROM
[C:\Users\liron\Downloads\Example_Calc.xlsx]
(ooxml, embedded labels, header is 2 lines, table is Sheet1);

Table2:
 LOAD  Months_since_joined1, 
     JoinedMonth_Max, 
     JoinedMonth_Min
  FROM
[C:\Users\liron\Downloads\Example_Calc.xlsx]
(ooxml, embedded labels, header is 2 lines, table is Sheet1);


IntervalMatch (JoinedMonth) load JoinedMonth_Min,JoinedMonth_Max resident Table2;

check out the attach app 

[MDH1]

Ah, that's really helpful - many thanks. I can't open the attachment it turns out given personal ed of qlikview, but I've imported using your script and created the table: so dimension as Months_since_joined1, Total Initial Balance =sum(Balance*first_entry) and Total Paid Amount = sum(if(Months_since_joined=Months_since_joined1, Paid_Amount)). Let me know if there was a different tack you took.

Just to double check - there's no way to achieve a similar outcome in the chart expressions without the script amendment as far as you're aware? 

[lironbaram]

Hi , 
you might be able to create a calculation in the chart but it will have a performance impact.
as rule of thumb if you can connect tables in the script you should do it, as it give better performance and give your users more option to filter the data