Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

help with prior YTD

I've been struggling with creating a measure which will sum my sales amount for prior YTD.

I thought it was the format of the dates in my results, so I went as far as to use TODAY as the both base date and the date to compare and still cannot get a "true" result.

Here is what my measure is set to, TestDate is loaded by the load script from Today(), SalesAmount is the numeric sales value which I want to summarize.

Sum(if(InYearToDate(TestDate, TestDate), 0), SalesAmount))

I would have expected this to simply summarize ALL rows in my table, but instead the result is 0.

What am I misunderstanding?

6 Replies
reddy-s
Master II
Master II

Hi Florence,

Create a field in the master calendar table or in the table where th date field exists as below:

inYeartoDate(DateField,today(),-1) * (-1) as LYTD // this will flag all the dates which fall in the last year to date as 1

Now create a measure:

sum({< LYTD = {1} >}SalesAmount)


This will give you a sum of Sales for the last year to date period.


Thanks,

Sangram.

jagan
Luminary Alumni
Luminary Alumni

Hi,

Check this links, hope it helps you

Set Analysis for certain Point in Time

Calendar with flags making set analysis so very simple

Set Analysis for Rolling Periods

Not applicable
Author

Thank you very much - how would I do that same thing without storing the on/off value in the database?  I'd like to actually determine if it is PYTD in the chart because my data is coming in via incremental loads, so it is likely that what previously wasn't included in PYTD will at some point in time be included.

reddy-s
Master II
Master II

Hi Florence,

You do not have to worry about it at all. I totally understand what you are trying to say. For example when you see the dashboard tomorrow, same day last year should also be included in the sum as it falls under Previous Year to day.

The master calendar should be in the transformation file and has to run every day. This will create new flags every day based on the current day. So your calculations will always remain correct.

Thanks,

Sangram Reddy.

Not applicable
Author

thank you for your answers.  I believe that you understand what I'm trying to accomplish, but I'm still not clear.

I have a sales history table that I am loading once a day, adding to my existing sales history which is stored in a qvd file.

how do I reevaluate my "flags" which tag rows in the table coming in from the qvd file as Prior Year To Date?

can you explain what you mean by "master calendar"?

reddy-s
Master II
Master II

Hi Florence,

A master calendar will have flag fields for YTD,QTD,MTD ....and so on. This will help you in calculations.

Check this post about master calendar and you will understand everything.

The Master Calendar

Thanks,

Sangram.