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: 
rpavan17
Creator
Creator

Calculate Every Month End Closing Balance/Amount

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

11 Replies
Anil_Babu_Samineni

Try this

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Taoufiq_Zarra

Hi,

in the load script?

can you share sample data ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
rpavan17
Creator
Creator
Author

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

rpavan17
Creator
Creator
Author

It gives null value

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

rpavan17
Creator
Creator
Author

Hi,

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

 

Kushal_Chawda

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

rpavan17
Creator
Creator
Author

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

This is just a date.

Kushal_Chawda

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)

rpavan17
Creator
Creator
Author

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