topic Re: Calculate expression from two fields from two different tables in QlikView

Calculate Every Month End Closing Balance/Amount

rpavan17 — Sat, 16 Nov 2024 19:25:00 GMT

Hi,

I have two Tables

Table 1:

Vendor,

Date_Posting,

Date_Clearing,

Amount

Table2:

Month_End_Date;

I have to get the sum of Amount where Date_Posting < Month_End AND Date_Clearing > Month_End AND

SUM(AMOUNT)>0 FOR EACH VENDOR .

Can you please help me here?

If i filter with vendor, it should show only selected vendor amount

Many Thanks

Re: Calculate expression from two fields from two different tables

Anil_Babu_Samineni — Fri, 20 Dec 2019 08:14:46 GMT

Try this

Sum({<YourPrimaryKey={"=Date_Cleaning>Month_End and Date_Posting<Month_End"}>} AMOUNT)

Re: Calculate expression from two fields from two different tables

Taoufiq_Zarra — Fri, 20 Dec 2019 08:20:22 GMT

Hi,

in the load script?

can you share sample data ?

Re: Calculate expression from two fields from two different tables

rpavan17 — Fri, 20 Dec 2019 09:17:25 GMT

Data:
Load *Inline[
Company, Vendor, Date_Posting, Date_Clearing, Amount
5000, 147, 02-02-2019, 06-06-2019, -245
4000, 157, 03-03-2019, 07-07-2019, -234
5000, 157, 03-04-2018, 11-09-2019, -244
5000, 154, 02-03-2019, 06-07-2019, 245
5000, 147, 02-02-2018, 06-08-2018, -322
5000, 134, 02-02-2018, 06-08-2018, -312
4000, 157, 01-10-2019, 03-11-2019, 554
];

Month_End:

Load Distinct
Date(Monthend(Month),'DD-MM-YYYY') as Month_End

Resident Master_Calendar;

Master_Calendar contains dates from last few years.

Expecting Dimension as Month_End, to see the amount which are greater than 0 in measure

Re: Calculate expression from two fields from two different tables

rpavan17 — Fri, 20 Dec 2019 08:51:34 GMT

It gives null value

Sum({<VendorCompany={"=Date_Cleaning>Month_End and Date_Posting<Month_End"}>} AMOUNT)

Re: Calculate expression from two fields from two different tables

rpavan17 — Fri, 20 Dec 2019 08:57:01 GMT

Hi,

Expecting Dimension as Month_End, to see the amount which are greater than 0

Re: Calculate expression from two fields from two different tables

Kushal_Chawda — Fri, 20 Dec 2019 09:53:21 GMT

Does Table 2 Contain Vendor? Id the Month_end_date is static value?

Re: Calculate expression from two fields from two different tables

rpavan17 — Fri, 20 Dec 2019 11:10:48 GMT

Month_Date is static value and does not contain vendor in this table.

This is just a date.

Re: Calculate expression from two fields from two different tables

Kushal_Chawda — Fri, 20 Dec 2019 12:01:09 GMT

I have two Tables

Table2:

Month_End_Date

FROM Table2;

let vMonthEnd = peek('Month_End_Date',0,'Table2');

Table 1:

Vendor,

Date_Posting,

Date_Clearing,

if(Date_Posting < $(vMonthEnd) and Date_Clearing > $(vMonthEnd),1,0) as Flag

Amount

FROM Table1;

Now you can use the expression as below

=Sum({<Flag={1}>}Amount)

Re: Calculate expression from two fields from two different tables

rpavan17 — Sat, 21 Dec 2019 00:55:00 GMT

I would like to see only sum greater than 0 for everymonth end date in calendar

Re: Calculate expression from two fields from two different tables

rpavan17 — Tue, 24 Dec 2019 13:50:55 GMT

Sample File is here

Re: Calculate expression from two fields from two different tables

Brett_Bleess — Thu, 26 Dec 2019 14:56:36 GMT

About the only thing I can offer at this point is the Design Blog area base URL, you can have a search in there to see if you can find any posts that may help. This area consists of mostly how-to related items written by our internal experts, so you may find something there to help get on track. My post will pull things back to the top too, but given the holidays, not sure who is actually out here right now.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett