ytd expression error

[nagasekhar]

=Sum({<OrderDate={">=$(=yearstart(addyears(max(OrderDate)),-1)<=$(=addyears(max(OrderDate)),-1))}>"}Sales)

this expression showing error[error in set modifier expression]

how to resolve it

[Qlik1_User1]

@nagasekhar Try this, its working for me.
=Sum({<OrderDate={">=$(=yearstart(addyears(max(OrderDate),-1)))<=$(=addyears(max(OrderDate),-1))"}>}Sales)

[nagasekhar]

2009 sales

[marcus_sommer]

Put your expression into a table-chart and don't add a label for this expression. Afterwards you could see by hoovering over the label how the ad-hoc variables in your set analysis are evaluated. Both parts are working and show the expected values?

- Marcus 

[nagasekhar]

i am getting in  bar chart but error in  kpi's

[marcus_sommer]

There is really the identically expression in the bar-chart and the text-box? Is the year from the bar-chart directly related to the OrderDate? How does the $-sign expansion evaluation of the applied adhoc-variables look like in the label like suggested above?

- Marcus  

[nagasekhar]

1.we extracted year from orderDate

2. sum(sales) vs year in bar chart

in kpi's we have takes orderDate for ytd expression

[marcus_sommer]

Did you already look how the adhoc-variables within the set analysis are evaluated like above mentioned. Usually it gives valuable hints to what's happening. This may a quite different date like you expects or the applied formatting caused the trouble which may in your case just interpreted as the division of Day/Month/Year ... Therefore the general recommendation to make never a matching or any calculation with a formatted value ... just for the future.

Now you may just wrap your date-extracting with a num() like:

'$(=num(yearstart(addyears(max(OrderDate),-1))))'

- Marcus

[nagasekhar]

hai 

marcus sorry for troubling you

i will send binary load qvw

can you please solve it for me

2009 sales(reason for not getting)

and how to resolve it

[marcus_sommer · Accepted solution]

You need something like this:

=Sum({< Year, OrderDate = {">=$(=date(yearstart(addyears(max(OrderDate),-1)), 'DD/MM/YYYY'))<=$(=date(addyears(max(OrderDate),-1), 'DD/MM/YYYY'))"}>} Sales)

whereby your trouble is caused from different formatting. Your OrderDate is 'DD/MM/YYYY' but your default format for dates within the application defined by the interpreting-variables at the script-start in tab Main is 'MM/DD/YYYY'.

IMO all dates and their interpretation within an application should have the same format und further I suggest to avoid all such trouble by using pure numeric values for each kind of matching and calculations. This means in your case just to double the OrderDate with an additionally floor(OrderDate) as OrderDateNum and then you could mostly use num() or floor() to call the comparing/calculation wherever you need it.

- Marcus