Comparison of month of an expression and the one in the dimension

[#1]

hi everyone,

I need to do a comparison in a pivot table. Explaining myself better:

The invoices have a due date & I need to show when those invoices havent been paid.

My pivot table has the following columns: MONTH | 30 Days | 60 Days | 90 Days and so on...

Eg: an invoice due date is: 12-01-2010. If at 12-02 -2010 havent been paid yet, so it has 30 days out-of-date or expired, I should then add up the amount of that invoice to the column: 30 days of the month FEB , because at february it has 30 days expired. In other case, if the due date of an invoice were 12-11-2009,

In order to get that in the expression of the column 30 days: I add to the due date a month: addmonths(VFACTURA.due_date, 1)

and then I compare the month returned of the function above with the one of the pivot table (MONTH: VFACTURA.due_date): if (month(addmonths(VFACTURA.due_date, 1))=VFACTURA.due_date......

so, my expression 30 days: sum(if (month(addmonths(VFACTURA.due_date, 1))=VFACTURA.due_date ,VFACTURA.tot_neto))

the result that's been returned is 0 (zero), i guess it's because of the comparison month(....) = VFACTURA.due_date.

So, How can I compare, in an expression, a month (calculated in it) with the one of the dimension???

Hope you understand, Thxs in advance!!

[#2]

Well you can do this straight forward, the only thing I see in your expression is that you are comparing a month with an actual date, thats why is returning 0, yuo have to compare months with months and dates with date. On the other hand, you can use the function class to create a dimmension if you have even intervals, in this case 30-day interval. Your dimmension expression might look something like class(today() - VFACTURA.due_date, 30) in order to skip the pain of calculating this with expression.

Regards

[#3]

hi Ivan, thxs for your response.

I can't see where I supposedly compare a month with the actual date :S if i never use today() function.

In fact, i dont really want to compare with the actual date, 'cause the table it's about the last year.

Any other idea?

[#4]

Hi leonor, you are right. You can always substitute today() for you base date, which might be something the user selected. For example, as in your first post, if your base date is 12 - 02 -2010 then you can substract the invoce due date 12 - 01 - 2010 and get it clasified in the 30 days range.

If you need some more help, probably you can attach a sample qlikview app.

Regards

[#5]

Hi ivan, thxs for your answer.

Tthe issue is that the 30 days have to be after the due date... so, i dont have to substact.

What's my issue is to compare a calculated month of an expression with the month of the dimession.

Something like: if the month returned in the expression it's January then add it amount ONLY IF the month of the row (dimenssion) it's January.

The month of the dimenssion it correspond to the month of the due date, but i'm not sure that have influence in my question..

thxs in advance

[#6]

Hi Leonor, what I was trying to say in my first post is that in the expression you posted, you seem to be comparing a month with a date,

sum(if (month(addmonths(VFACTURA.due_date, 1))=VFACTURA.due_date ,VFACTURA.tot_neto))

Notice that the function month will return you an integer representing the month number, and that is something you cannot compare with a date VFACTURA.due_date. You can try the following expression if you want to compare it with the Month, assuming your field dimmension is called "Month"

sum(if (month(addmonths(VFACTURA.due_date, 1))=Month ,VFACTURA.tot_neto))

Regards

[#7]

Hi Ivan, thxs for your response.

About VFACTURA.due_date , It was really my mistake translating. I was in fact comparing with the month of the due_date.

I had tried already if the first place your recommendation of compare with MONTH (assuming that's the name of my dimension) and it did not work.

Any other idea?

Thxs in advance

[#8]

Hi leonor, I can't think what it is not working it should, I've done this a thousand times, I'm guessing there might be an issue between data types. I recommend you to post your issue with dummy data so I can give you an straight answer.

Regards

[#9]

I just came up with something. Probably what you should do is having a calculated monthyear with the actual monthyear in your dimmension.

For example:

sum(if (monthname(VFACTURA_date, 1)=MonthYearDimmension ,VFACTURA.tot_neto))

[#10]

oook...how can I call to a calculated monthyear of my dimension in a expression?

eg: i created a calculated dimension with the following formula: month(VFACTURA.fec_venc)

but in my expresion, how do i ask for it?? how do i ask for MonthYearDimension??

resuming, what is for you MonthYearDimension... ?

Thxs!