topic Re: Sumif using calculated columns for if conditions in QlikView

Sumif using calculated columns for if conditions

MDH1 — Sat, 16 Nov 2024 17:38:52 GMT

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.

Re: Sumif using calculated columns for if conditions

lironbaram — Tue, 05 Jan 2021 18:22:53 GMT

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

Re: Sumif using calculated columns for if conditions

MDH1 — Tue, 05 Jan 2021 19:14:37 GMT

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?

Re: Sumif using calculated columns for if conditions

lironbaram — Wed, 06 Jan 2021 08:54:02 GMT

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