Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Set analysis sum where date from other table is equal to date in the dimension

I have two separate tables, one of them has customer data and the other has accounting data.

Both tables share a common key so that I can link accounting data to customers, but I want to keep both tables separate because I want to summarise data from accounting for each customer record and want to avoid row duplication. 

Lets say these are my tables: 

 

 

 

 

 

I want only data from the customer in the dimensions but want to sum accounting data in the expressions so that I can show postings on account "123456" in one expression and postings on account "56789" on another expression. I want to show postings depending on the key and also to sum the amount only when this is smaller than or equal to the reporting date.  In this way I want to show a rolling balance depending on the reporting date being looked at.

I tried this set expression but it does not work:

 

=Sum({1<posting_date={"<=$(=reporting_date)"}, account={'123456'}>}amount)

 

 

I also want to show all customer data even if there are no postings on the account.

Appreciate any tips. 

Sample qvw attached.

Labels (6)
5 Replies
Highlighted
Employee
Employee

I think that due to the combinations of values  related based on they key there are ambiguous results so this table and expression will not work. When you select one reporting date, you get a result.

Highlighted
Partner
Partner

Do you mean like this?

MC.PNG

In script,  do not link the key with the two tables.

Rename the key field so that the tables doesn't link.

Then in your expression, try this:

=Sum({<posting_date={"<=$(=max(reporting_date))"}, account={'123456'}>}if(key=keyAccount, amount))

Refer attached qvw as reference.

Thanks and regards,

Arthur Fong

Highlighted
Contributor II
Contributor II

Not quite like that. I want to display the reporting date and only postings where the posting date is less than or equal to the reporting date. We would have different sums for September and October. The way you did it, you have the same balances in September and October because you used max(reporting_date).

Highlighted
Partner
Partner

Can you please attach an expected result?

Highlighted
Contributor II
Contributor II

This is a picture of the expected resultexpected result.png