Current Day

[#1]

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

[#2]

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

[#3]

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

[johnw]

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)

[#5]

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

[nagaiank]

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.

[#7]

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

if

({{<[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

[#8]

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

[johnw]

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.

[#10]

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