topic Re: YTD Dates Issue in QlikView

YTD Dates Issue

engr_farhanqadr — Thu, 06 Nov 2014 20:11:34 GMT

Dear Folks,

please guide me how can I pick YTD quantity of previous year like its 7 Nov 2014 today , so YTD of previous will be 7 Nov 2013

my table columns are below, the main date column is [date/time] and quantity column is 'quantity'. I want to show in straight table as two column QTY as current year quantity and PQTY as previous year quantity.

I have data of two year 2014 and 2013.

table 1:

LOAD

WeekEnd([date/time]) as weeks,

[date/time],

'T' & ceil(Day([date/time])/10) as Tenant,

MonthName([date/time]) as months,

Year([date/time]) as Year,

date(floor([date/time])) as cdate,

[settlement id],

type,

[order id],

sku as prod_sku,

description,

quantity,

marketplace,

fulfillment,

[order city],

[order state],

[order postal],

[product sales],

[shipping credits],

[gift wrap credits],

[promotional rebates],

[sales tax collected],

[selling fees],

[fba fees],

[other transaction fees],

other,

total

please help out

Farhan

Re: YTD Dates Issue

JonnyPoole — Thu, 06 Nov 2014 21:31:09 GMT

You can use SET ANALYSIS in the UI expression to do this.

Another popular way to do this is to create a flag field in the data load, for example as follows:

Then when you build your chart expression (ie: Sum(Sales) ) , multiply it by the YTDFlag field... any value outside of YTD will be zero and not be included in the tally. (ie: sum(Sales * YTDFlag) )

table 1:

LOAD

WeekEnd([date/time]) as weeks,

[date/time],

if( Year([date/time]) = Year(Today()) , 1, 0) as YTDFlag,

etc....

Re: YTD Dates Issue

engr_farhanqadr — Thu, 06 Nov 2014 22:15:00 GMT

Dear Jonathan,

I did the same but in both YTD and PYTD column values are same I want data of 2014 in YTD column and data of 2013 in PYTD column , I used sum(quantity* YTDFlag) ) in PYTD column and sum(quantity) in YTD column.

please advise.

Re: YTD Dates Issue

jduenyas — Thu, 06 Nov 2014 23:04:53 GMT

Try this

Count({<[Date/Time] = { ">=$(MakeDate(Year(today())-1,Month(today()),Day(today())))"} >}YourFieldToCount)

Re: YTD Dates Issue

jduenyas — Fri, 07 Nov 2014 00:00:17 GMT

Sorry for the error in my previous post

Try this for prior year:

Count({<[Date/Time]={"=${=makedate(Year(today())-1,Month(today()),Day(today()))<makedate(year(today())-1,Month(today()),day(today())+1)" }>}YourFieldToCount)

Re: YTD Dates Issue

JonnyPoole — Fri, 07 Nov 2014 01:02:31 GMT

The technique is sound but i don't know what you did in your load script .

Can you share what you did in the script and also a screenshot of the chart you are using to calculate this.

Re: YTD Dates Issue

djsampat — Fri, 07 Nov 2014 01:13:05 GMT

Hi,

View this sample YTD

YTD Calculation by Month

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

Regards

Dhruv