Set Analysis statement issue

[raghuvr33]

Hi All,

I am not sure what is wrong with the set statement below. I am trying to sum [invoice net price] for previous year than the selected date keeping all other current selections same. The [invoice year] has been generated in the script using year(invoice_date) statement. I tried and displayed ( [invoice year]  -1 ) and $(= year(VStartInvDate) - 1) in a text box and both display 2013 but am not sure if there's any other data like text that is making it not work.

Please help!!!!!

Sum(

{
<
[Invoice Year]=, [Invoice Year]= {$(= year(VStartInvDate) - 1)}
>
}
[Invoice Net Price])

[#2]

Sum( { $ < [Invoice Year]= {$(= year(VStartInvDate) - 1)}> } [Invoice Net Price])

What is VstartInvDate a variable assigned every time you have some selection on Date? otherwise try

Sum( { $ < [Invoice Year]= {$(= only(year([Invoice Year]) )- 1)}> } [Invoice Net Price])

[raghuvr33]

Thanks for the reply. Yes VstartinvDate is a variable that user chooses the start date based on which am trying to sum data for previous year.

The expression    $(= year(VStartInvDate ) - 1)  gives the result 2013 correctly based on 2014 vStartdate when displayed in a text object.

Am totally at a loss understanding why it is not working in a set statement as no data gets returned. Is the format of the result from the above statement seperate from the format of year(invoice_date) ?? (which is how  [Inovice Year] is calculated currently in script)

[PrashantSangle]

Dear Raghu,

Little confuse with what you are trying to do?

Since in your set analysis you are nullifying the [Invoice Year] and in same set analysis you are assigning value to [Invoice Year] Field.

How could this work.

Try with only one condition. Either assign value to field or ignore it.

Try like

Sum({<[Invoice Year]={"$(= year(VStartInvDate) - 1)"}>}[Invoice Net Price])

Regards

[#5]

Do you have Year and month as dimension? if true, then try to put just month and make two expressions one for current year and another for previous year.

[raghuvr33]

Dear Max,

My understanding was to ignore the current selection for particular field we have to add statements like [Invoice Year]=, . By doing this it will take current selection for everything except invoice year which is being set to previous year based on the calculation  [Invoice Year]= {$(= year(VStartInvDate) - 1)}.

That's the reason for nullifying and chances are i may be wrong in what i understand as i am quite new to QV. I did try like you suggested and am getting the same blank results. Do you believe it is a data format issue with [Invoice Year] and the calculation $(= year(VStartInvDate) - 1) ???

[raghuvr33]

Just to check if there were any formatting issues added expression in a text object

if

(([Invoice Year] - 1) = $(= year (VStartInvDate )- 1),'True','False')

and it returned 'True'. So appears there are no data format issues but still not able to get it to work in  a set statement.