Dates

[Anonymous]

Hello,

I've been watching videos on how to work with dates with Qlik. I have successfully formatted my dates as MMM-YY. This field is called Billing Period in the data that I'm working with. When I use a function such as =Max(Billing Period) it correctly shows the max Billing Period in my data. Yet, when I attempt to use set expression it doesn't work. I'm attempting to use the below function:

=sum({$<Date=$(=Max([Billing Period]))>}[Billing Amount])

What I want to ultimately accomplished is to be able to do Month over Month variances and rolling variances (13 months) based on the selection of data. I think first I need to understand why the above function doesn't work. If you also help me understand how to apply the function to do month over month variances and 13 months variances that would be extremely helpful.

Regards,

[vamsee]

Hi,

You said your dates are formatted as MMM-YY. This does not have Date in it (DD).

Try using Makedate() to create date.

sum({$<Date={"$(=Max([Billing Period]))"} >}[Billing Amount])

[Anonymous]

I tried sum({$<Date={"$(=Max([Billing Period]))"}>}[Billing Amount]). It didn't work for me. Thanks for trying. I formatted my date as MMM-YY in the Data Manger view. I don't know if that makes a difference or not.

[vamsee]

try adding =Max([Billing Period])) in a text box, date in list box and see if the format is same for both.

If Yes, select the matching date, add a text box with the following condition and see

=if(Date=Max([Billing Period],'TRUE', 'FALSE')

[JustinDallas]

Does Qlik know how to compare strings that are formatted like 'YYYY MM'?  In my personal opinion, since I have 'Billing Period', then I can create a date such as 'Start Of Billing Period' from my BillingPeriod.  In that case, I would have a Date object which would have a well defined metric that Qlik could use out of the box.