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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using Billing Periods instead of Months

I am striking out on this set analysis....

I have the following columns in my data set:

CHG AMT:  Amount that was charged for the item

CHG Date:  The date the item was charged

CHG Period: It is a Year/Month combination using our BILLING Calendar.  So the 1st of every month belongs to the month before.

                              Ex.  CHG DATE= March 1, 2012

                                     CHG Period =  201202

                                             BUT

                                    CHG DATE= March 2, 2012

                                     CHG Period =  201203

I am trying to get a constant that represents the total amount billed (CHG AMT), for the most recent [CHG Period] based off the recent date in the file (Max(CHG DATE))

Sample Data:

CHG AMT               CHG Date                    CHG Period

15.50                       2/29/2012                        201202

20.75                       3/01/2012                        201202

10.25                       3/01/2012                        201202

  5.50                       3/02/2012                        201203

  8.00                       3/03/2012                        201203

10.65                       3/03/2012                        201203

Max(CHG DATE)=3/3/2012

Which is Period--> 201203

Sum of Charges for all of 201203: Expected Answer $24.15

Any Advice?

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

=sum({<[CHG Period]={"$(=max([CHG Period]))"}>}[CHG AMT])


talk is cheap, supply exceeds demand

View solution in original post

9 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

This should return the sum of CHG AMT for the maximum CHG Date:

sum({<[CHG Date]={'$(=max([CHG Date]))'}>} [CHG AMT])


talk is cheap, supply exceeds demand
Not applicable
Author

I seem to be getting 0 for an answer...

I added a sample to my original post that contains the simple data set from my sample data.

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Your dates have a different format than the document default. Try formatting the date:

=sum({<[CHG Date]={"$(=date(max([CHG Date]),'M/DD/YYYY'))"}>}[CHG AMT])


talk is cheap, supply exceeds demand
Not applicable
Author

That fixes the formatting issue but the total CHG AMT comes out to $18.65.  It is only summing the Max(CHG Date).  What I am looking for is the total CHG Period that the Max(CHG Date) is in.

So if 3/3/2012 is the max date.... that is in CHG Period 201203

so the answer would be the sum of all dates in the 201203 CHG Period, which equals $24.15

Thank you for your help, I really appreciate it.

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

=sum({<[CHG Period]={"$(=max([CHG Period]))"}>}[CHG AMT])


talk is cheap, supply exceeds demand
Not applicable
Author

Thank you so much!  That worked like a charm.  I think I was over complicating it for no reason.

Thanks Again!

Not applicable
Author

Hi Gysbert,

Can I ask you one more question....

Do you know why the 1 is not working to ignore the selection?

=NUM(sum({1<[CHG Period]={"$(=(max([CHG Period])))"}>}CHG_AMT),'$###,000.00' )

Thanks

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

max([CHG Period]) still takes selections into account. If you want this to always be the absolute maximum then change it to max({1}[CHG Period])


talk is cheap, supply exceeds demand
Not applicable
Author

I just learned something new! I did not know you could put the {1} there.  All the examples I was looking at showed it in the beginning before the <>.

Thank you for all your help, and your SUPER fast reply!