Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

need help with chart expression and set analysis

Hi All ,

Attached QVW file has all the information in it. Please let me know in case of any questions. Thanks for your help

1 Solution

Accepted Solutions
sunny_talwar

Straight table solution

Capture.PNG

Sum(Aggr(If(vDocumentDate - [Due Date] < 1, Sum({$<[Document Date_BLDAT]={'<=$(vDocumentDate)'}, Clearing_AUGDT={'>$(vDocumentDate)'}>} [Amount in LC_DMBTR])), [Document Number_BELNR], [Document Date_BLDAT], [Due Date], Clearing_AUGDT))

View solution in original post

11 Replies
Anonymous
Not applicable
Author

pfa

sunny_talwar

Do you have to use a straight table or a pivot table might also work?

sunny_talwar

Straight table solution

Capture.PNG

Sum(Aggr(If(vDocumentDate - [Due Date] < 1, Sum({$<[Document Date_BLDAT]={'<=$(vDocumentDate)'}, Clearing_AUGDT={'>$(vDocumentDate)'}>} [Amount in LC_DMBTR])), [Document Number_BELNR], [Document Date_BLDAT], [Due Date], Clearing_AUGDT))

sunny_talwar

Here is a pivot table based solution

Capture.PNG

Anonymous
Not applicable
Author

Sunny need in pivot table what is the expression

sunny_talwar

Dimension

=month(vDocumentDate)

=Aggr(

  If(vDocumentDate - [Due Date] < 1, Dual('Current', 1),

  If(vDocumentDate - [Due Date] >= 1 and vDocumentDate - [Due Date] <= 30, Dual('1 - 30', 2),

  If(vDocumentDate - [Due Date] >= 31 and vDocumentDate - [Due Date] <= 60, Dual('31 - 60', 3),

  If(vDocumentDate - [Due Date] >= 61 and vDocumentDate - [Due Date] <= 90, Dual('61 - 90', 4), Dual('Over 90', 5))))), [Document Number_BELNR], [Document Date_BLDAT], [Due Date], Clearing_AUGDT)

Expression

Sum({$<[Document Date_BLDAT]={'<=$(vDocumentDate)'}, Clearing_AUGDT={'>$(vDocumentDate)'}>} [Amount in LC_DMBTR] )

Anonymous
Not applicable
Author

thank you Sunny. That definitely helps.

sunny_talwar

Awesome

Anonymous
Not applicable
Author

Sunny a small change in the request. Let me know if i have to do a separate post

if i have this DATE field which will have month end dates in case of the vDocumentDate variable. How can i get the expression get to work for each month. See the attached app you will have more information.