topic Re: Using Billing Periods instead of Months in QlikView

Using Billing Periods instead of Months

Mon, 25 Mar 2013 17:59:08 GMT

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?

Re: Using Billing Periods instead of Months

Gysbert_Wassenaar — Mon, 25 Mar 2013 18:10:22 GMT

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

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

Re: Using Billing Periods instead of Months

Mon, 25 Mar 2013 18:19:55 GMT

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.

Re: Using Billing Periods instead of Months

Gysbert_Wassenaar — Mon, 25 Mar 2013 18:50:32 GMT

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])

Re: Using Billing Periods instead of Months

Mon, 25 Mar 2013 19:00:29 GMT

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.

Re: Using Billing Periods instead of Months

Gysbert_Wassenaar — Mon, 25 Mar 2013 19:03:04 GMT

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

Re: Using Billing Periods instead of Months

Mon, 25 Mar 2013 19:15:38 GMT

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

Thanks Again!

Re: Using Billing Periods instead of Months

Tue, 26 Mar 2013 12:38:44 GMT

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

Re: Using Billing Periods instead of Months

Gysbert_Wassenaar — Tue, 26 Mar 2013 12:45:06 GMT

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])

Re: Using Billing Periods instead of Months

Tue, 26 Mar 2013 12:47:36 GMT

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!