YTD calculation at script level

[ananyaghosh]

Hi,

I am using the below code to calculate YTD based on Order Date:

[Temp Data]:

load sum(Sales) as [YTD Sales],  [Order Date] resident extract

where [Order Date] >= Date(YearStart(Max([Order Date]), 0), 'D/M/YYYY')  and [Order Date] <= Date(AddYears(Max([Order Date]), 0), 'D/M/YYYY')

group by  [Order Date] ;

and it gives me 'Unknown Error'.

Please let me know what is wrong.

Thanks,

Sandip

[Oleg_Troyansky]

Hi Sandip,

I think the problem is caused by using the MIN() and MAX() functions in the where clause - I doubt if this is a correct syntax.

What we usually do is calculate MIN and MAX upfront, store them in two variables, and then use the variables in the next LOAD statement.

Also, we don't typically pre-calculate the YTD amounts - instead, we calculate a calendar Flag for Current YTD dates and for Prior YTD dates, and then use the flags in chart expressions - you can either multiply the amounts by the flags, or use Set Analysis to filter by flags.

You can learn these techniques, along with many other advanced Qlik techniques, from my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

cheers,

Oleg Troyansky