topic YTD Calc !!! in QlikView

YTD Calc !!!

Mon, 05 Jul 2010 09:03:10 GMT

Just a question if I do the following it works, it's a year to date calc

sum( {$<Period ={">=$(=date(yearstart(today(),-1),'YYYYMM')) <=$(=date(addmonths(today(),-13),'YYYYMM'))"}, [Product Manufacturer] = {'Aspen-Pharmacare'} >} Sales )

But now when I want to do the following it doesn't work (Have a look at the period)

sum( {$<YR*100+MTH ={">=$(=date(yearstart(today(),-1),'YYYYMM')) <=$(=date(addmonths(today(),-13),'YYYYMM'))"}, [Product Manufacturer] = {'Aspen-Pharmacare'} >} Sales )

That is the calc to get to my period, its in the back end, but when I do it in the front end, it incorrect, I think its got something to do with the Syntax I think it needs quotes etc like

sum( {$<=$("YR*100+MTH") ={">=$(=date(yearstart(today(),-1),'YYYYMM')) <=$(=date(addmonths(today(),-13),'YYYYMM'))"}, [Product Manufacturer] = {'Aspen-Pharmacare'} >} Sales )

Please assist!

Thanks in advance J

YTD Calc !!!

sparur — Mon, 05 Jul 2010 09:08:51 GMT

Hello,

What is YR*100+MTH ? It's fieldName? As I know, in set Analysis you can use FieldName only.

YTD Calc !!!

Mon, 05 Jul 2010 09:13:43 GMT

YR is a Field Name as well as the MTH

They look like this YR = 2009

MTH = 1

So YR*100+MTH = 200901 That creates a "period" Or YearMonth...

YTD Calc !!!

Miguel_Angel_Baeyens — Mon, 05 Jul 2010 09:21:51 GMT

Hello,

Although I would create that field in the master calendar so you can reference it as "period" or "year-month" field (or dimension if any), you can check the year and month (that you already have as YR and MTH) from the date field.

Regards.

YTD Calc !!!

Mon, 05 Jul 2010 09:56:08 GMT

Correct, that is the way of doing it on the most correct way i suppose, but if can have the syntax to do this in the front end, i can use it on string values etc. To not pass a Field but a calculation in set analysis.

Read My first post,

Thanks,

YTD Calc !!!

Miguel_Angel_Baeyens — Mon, 05 Jul 2010 10:42:01 GMT

Hi,

Say you don't have a "Year" field but you can check for a given year in your Set Analysis. This is one way to do it:

{< DateField = {"=Year(DateField) = '2009'"} >}

The first part of the set condition must be always a field name. So you will need to check first for year (2009), then for month (1), to get the period you want

{< DateField = {"=Year(DateField) = '2009'"}, DateField = {"=Month(DateField) = 'jan'"} >}

but I understand that you already have year and month field, although not in 'YYYYMM' format, and that's why you I'd rather separate both.