topic month to date max value in QlikView

month to date max value

Thu, 21 Feb 2013 14:05:50 GMT

Hello,

I would like to compare two values between the same dates.

My formula is :

Sum (if(InMONTH(cal,timestamp(reloadTime()),0),codep_premier))-sum (if(InMONTHtodate(cal,ReloadTime(),-1),codep_premier))

Problem is I would like to replace my ReloadTime by the max of the calendar because sometimes the max will be 2, 3 or 4 days before I reloaded my application.

I tried to put "max(cal)", it didn't work, I tried timestamp(max(cal)) didn't work either. I pretty much tried everything, and when I just put max(cal) or others in a random text it works, but everytime i try with inmonthtodate it doesn't.

How can I solve this ?

PS: I'm using Qlikview 10.00 SR4.

Regards,

Re: month to date max value

giakoum — Thu, 21 Feb 2013 14:34:02 GMT

This should be done by set analysis, something liek the example below :

sum({$<Month={$(=max(Month))} codep_premier) -

sum({$<Month={$(=max(Month)-1)} codep_premier)

Provided that Month is a number.

Re: month to date max value

Fri, 22 Feb 2013 09:46:23 GMT

Hello Ioannis,

I'm quite new xith Qlikview and I found this document online talking about set analysis but I still don't know how to use it.

http://community.qlik.com/servlet/JiveServlet/previewBody/1867-102-1-1858/QlikLearn-Set-Analysis-Presentation.pdf

My calendar table is the following :

"cal" for the day

"month_codep" for the month

"codep_premier" is the value I'm trying to sum up.

When I do :

sum({$<month_codep={$(=month(max(cal)))}>} codep_premier)

it show a wrong number, higher than the sum of codep_premier for the whole previous month

sum({$<month_codep={$(=month(max(cal))-1)}>} codep_premier) shows only 0.

I'm having hard time correcting this code since I don't understand it.

Thanks in advance,

Re: month to date max value

giakoum — Fri, 22 Feb 2013 10:36:52 GMT

this looks correct to me :

sum({$<month_codep={$(=Month(Max(cal))-1)} codep_premier)

if it gives you 0 then maybe there is a data issue or we should take year in account also.

A sample application with sample data would help!