YTD, MTD, QTD, WTD Logic as compare to Last Year

[er_mohit]

Hi

M stuck off facing this problem to analysis with Last Year comparison

I m using the expression using set analysis.

I m giving you an overview that my calendar starts from  1st jan every year and week based on Friday to Saturday. So, my date should be different if i compared it with last year.

In every month having 4 weeks but for every quarter end has 5 weeks.

So, my Month field based on weeks.

Suppose today is : 13-11-2014 (13 November 2014)

So, for case of YTD it should be fine for comparing Current year and previous year

but for MTD : November Month 25-10-2014 to 13-11-2014 (Count of days is 20)

Should be fine for current year

but for making the logic last year November Month (LYMTD😞 i want 20 days of sale irrespective of whole month count like

Time period 26-10-2013 to 14-11-2013 count days is 20 (So it based on Days irrespective of Date Field)

QTD :

Current year having 48 days (Sum Sale)

Last yr(LYQTD) -  Also want 48 days ( (Sum Sale) )

WTD (47):

Current Year(WTD) :having 6 days

Last Year(LYWTD) also want 6 days for calculating the Sum(Sale)

Any help will be appreciable.

Regards

Mohit

[teempi]

Hi,

A trick I've used many times in cases like this is creating a special calendar that has two rows for one date. In your case for example, you could have a key date for 25-10-2014 and then link it two different dates - namely 25-10-2014 (current) and 25-10-2013 (previous year). You must also create a flag for each row telling you if it's the original date or the comparison. Your final table could then look something like this:

CompareCalendar:

@DateKey	Date	Flag_IsOriginal
25-10-2014	25-10-2014	1
25-10-2013	25-10-2014	0
26-10-2014	26-10-2014	1
26-10-2013	26-10-2014	0
27-10-2014	27-10-2014	1
27-10-2013	27-10-2014	0

@DateKey links to the transaction data and Date is the new field to be used in charts etc (you can also create Month and Year fields based on this Date column). Flag_IsOriginal tells you if the row in question should show you the real values, or previous year comparisons.

In charts you have to use set analysis:

Current: Sum( {< Flag_IsOriginal={1} >} someField)

Previous Year: Sum( {< Flag_IsOriginal={0} >} someField)

So if you now select 2014 Cctober from your filters, "Current" expression should calculate dates 1-10-2014 - 31-10-2014 and the "Previous" expression should use 1-10-2013 - 31-10-2013.

Please note that you probably have to figure out the logic for calculating the correct previous year dates (@DateKey) based on your requirements but the general idea should still be valid.

Hope this helps!

-Teemu

[#3]

Day-to-Day relationship is quite difficult to solve in Qlikview, in fact, Master Calendars are designed to solve only Date-to-Date relationship.

To solved this trouble, I think the best solution to follow is the Fact Table Trasformation, for the following reasons:

- Temporal relations are dynamic (selections sensitive) and not variable-depe

- Two separate relations can be implemented in reports, wich are user-selectable alternately

- Set analysis for YTD,MTD,WTD comparisons become veary simple and standardizable

To perform this, you have to follow several steps:

1) Mark original fact table records with a custom 'actual' Type field, for example  ...LOAD...., 'actual' as Type.. for every record in fact table

2) Generate custom date-loockup, wich autogenerate, for every date, the previous-year-date (in your example: 13-11-2014 -> 14-11-2013)

3) Create MapDateTable: Mapping Load ActualDate(Key) -> PreviousYearDate(Map)

4) Resident Load a simmetric fact table, and concatenate it with the original one, replacing actual Type field with ... 'previousyear' Type and Date with ApplyMap('ActualDate','TableName') as ActualDate (every date is regressed to his previous year date, but maintains the same field name 'Date')

5) 'Cut' your custom date-loockup from 'min_fact_date' to 'max_fact_date' if you want

6) Generate YTD, MTD, WTD Flags (1,0) in your date-loockup using the native qlik functions

Now you have to set all expressions to Type={'actual'}, if exp is related to current year values, or Type={'lastyear'} if is related to the previous year values.

You can also easly implement YTD, MTD, WTD buttons. (ex. select in field -> YTDFlag -> 1, select in field -> MTDFlag -> 1, etc.).

Last clarification: master calendar must be loaded before these transformations, because is necessary to relate it only to real data.

Hope this will help you

[er_mohit]

Hi thanks for the reply. As mentioned above, its hard to do or to set a logic on back end at this stage. Could you send me an alternative for this on front end side using set analysis.

And also i sent you file for my calendar. and having separate tab. What i want please write a logic to count of days in a period as above mentioned(YTD--LYTD,MTD--LYMTD,QTD--LYQTD,WTD--LYWTD)  using set analysis.

If there's any other suggestion to doing this then do it.

Please refer to attached file.

Thanks and Regards

Mohit

[er_mohit]

Hi

please give me any suggestion. to solve this problem.

regards

Mohit

[#6]

I'm pretty sure you can' t set a relational model, whatever it is, without scripting on back end side.

[er_mohit]

Hi   jonathan dienst and tresesco

Pleas help me to resolve this on front end side.I m stuck off facing this problem.

Regards

Mohit

[tresesco]

Hi,

Check out the following date functions, I guess that could help in such customization:

InMonthsToDate(), InquarterToDate(), yearToDate()

[vikasmahajan]

PFA Sample.

Vikas

[er_mohit]

Hi tresesco

Thanks for your reply.

Hope you aware that my date field is not same with last year so, have to work on no. of days as mentioned above.

If you provide a sample related to this will be great appreciable.

Regards

Mohit