topic Re: Set Analysis statement issue in QlikView

Set Analysis statement issue

raghuvr33 — Sun, 28 Dec 2014 23:31:29 GMT

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

Re: Set Analysis statement issue

Sun, 28 Dec 2014 23:55:54 GMT

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

Re: Set Analysis statement issue

raghuvr33 — Mon, 29 Dec 2014 13:26:41 GMT

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)

Re: Set Analysis statement issue

PrashantSangle — Mon, 29 Dec 2014 13:32:53 GMT

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

Re: Set Analysis statement issue

Mon, 29 Dec 2014 13:33:48 GMT

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.

Re: Set Analysis statement issue

raghuvr33 — Mon, 29 Dec 2014 13:47:43 GMT

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) ???

Re: Set Analysis statement issue

raghuvr33 — Mon, 29 Dec 2014 14:12:21 GMT

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

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