topic Current Day in QlikView

Current Day

Wed, 06 Jul 2011 13:27:04 GMT

Hello,

I currently have a linearity chart that has the sums of three Fiscal Quarters. I also have the field [Day # in FQ], and so for the current quarter (2012 Q2), we are only up to day 46 for example. I need to create a chart that shows the total sums of [TOTAL REVENUE ($)] for all three quarters that the linearity chart is summing, but only up to the most current day in the most current fiscal quarter. So all three sums would be through day 46 in my example. Please let me know if you would have any idea how to go about accomplishing this, and if I can give you any more helpful information, and thank you so much for all your help.

Sorry I cannot upload any examples of my data because it contains confidential information regarding a publicly traded company.

Alex

Current Day

Wed, 06 Jul 2011 13:53:24 GMT

Hi,

Can you make a small QlikView application, that would ilustrate the issue?

It will help us to understand what you want to achieve.

Kind regards,

Janusz

Current Day

Thu, 07 Jul 2011 18:34:25 GMT

Ok here was the advice I was given by my dashboard support team. Unfortunetly they only have 2 people to help about 100 people, and so their responses are a bit delayed and not very detailed. Can anyone help me to interpret this using a specific examples?

"As for creating the flag in the calendar, this needs to be handled in the script. The pusedcode would be: What day number is it in the quarter? Day X. If day number is <= Day X then Y or else N. This will give you a flag for every date in your calendar that tells you if the day in the quarter has passed or not, and then aggregating over this flag where it is Y will give you relative quarter to dates."

I'm assuming they are telling me to create an variable based on the day in the current quarter (vDay#CurrentQTR), and then somehow use this variable to sum the revenue, with a null if the x> vDay#CurrentQTR?

Thanks,

Alex

Re: Current Day

johnw — Thu, 07 Jul 2011 20:10:54 GMT

I believe they're telling you to do this in the script where you load your calendar table:

,if(daynumberofquarter(Date)<=daynumberofquarter(today()),'Y','N') as QTDFlag

And this in the chart expression:

sum({<QTDFlag*={'Y'}>} Revenue)

Current Day

Fri, 08 Jul 2011 13:53:11 GMT

this is what i currently have written based on your recomendation John, and i have it in the load script on my main tab under the default system variables. I assume I'm doing something wrong as it's not loading and the as function is not the proper color. Any advice would be greatly appreciated. Thank you

set vCurrentDay= if({{<[Day # of FQ]{'Q2 2012'}>}<=[Day # of FQ](today()),'Y','N') as QTDFlag;

Alex

Current Day

nagaiank — Fri, 08 Jul 2011 13:59:54 GMT

Set analysis expressions do not work in load script. They work only in chart expressions.

So try to use script expression given by John or some variant of it based on your requirements.

Current Day

Fri, 08 Jul 2011 14:30:46 GMT

Sorry but I am fairly new to this, so in my load statement if statements do not seem to be working. It says the if statement is bad, and i cannot load my script. I put the statement after the [Fiscal Quarter and Year],

({{<[Fiscal Quarter and Year]{'Q2 2012'}>}<=[Day # of FQ](today()),'Y','N') as QTDFlag,

and then it won't load correctly, where should i be putting the statement?

thanks,

alex

Current Day

Fri, 08 Jul 2011 16:59:39 GMT

Ok, I think the problem is the format of the data. currently I have a field

[Fiscal Quarter and Year], which returns values such as '2012 Q2'

then linked to this is the field; [Day # in FQ], which returns values such as '32'

and I need to format these two fields into one readable by the today()) function

thanks,

alex

Current Day

johnw — Fri, 08 Jul 2011 17:28:11 GMT

Daynumberofquarter() is a QlikView function. You don't replace it with your field names, and you don't add set analysis syntax to it. You should have a calendar table. This calendar table should have a field like "Date", or perhaps "Fiscal Date" since you're talking about a fiscal calendar. You may need to use a second parameter on the daynumberofquarter() function if your fiscal quarter starts on some off month like November, say.

Current Day

Fri, 08 Jul 2011 17:49:33 GMT

Actually the fiscal calander starts off in February, and it has the fields 'Date', and 'Day # in FQ'

Sorry so what would my second parameter look like?

Thanks,

Alex

Re: Current Day

johnw — Fri, 08 Jul 2011 18:34:38 GMT

The 2 says that your fiscal calendar starts with February:

,if(daynumberofquarter(Date,2)<=daynumberofquarter(today(),2),'Y','N') as QTDFlag

That goes in the load script where you load your calendar, where you load your Date field into the calendar table. This is one more field you are loading, just like the other fields in your calendar.