Set Analysis

[Anonymous]

i have the following chart which compares values from one year to the next:

The 'Month Year' e.g. 'Dec 2013' are based on the following expression:

=Num(Sum ({$<Year={2013},Month=>} [MedAid Amount] + [Shortfall]), '$##,##0.00')

Since the year 2015 only contains data up to the end of August, growth comparisons with previous year becomes erroneous.  It would be good to base the above on rolling 12 months figures so that like can be compared to like (12 month values) when it comes growth rates.  How can the above expression be modified to calculate rolling 12 months values instead of calendar values?

Regards.

[Oleg_Troyansky]

There is a number of ways to calculate rolling 12 months averages, some more complex than others. I'd recommend to add a number of calendar flags to your Master Calendar table (assuming that you have one) and define the "Rolling 12 Months" logic there. You could have flags for the Last Rolling 12 Months, Previous Rolling 12 months, Two Years Ago, etc...

Then, your set analysis condition becomes rather simple:

=Num(Sum ({$<_CR12_Flag={1}>} [MedAid Amount] + [Shortfall]), '$##,##0.00')

Check out my new book QlikView Your Business. In the book, I describe in detail how to create and use conditional flags, including calendar flags. I also suggest creative ways to simplify your chart expressions and make them more flexible and reusable, when many calendar flags are involved.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[Anonymous]

Thank you Oleg, i will have a  look at the book.

Regards.

[Anonymous]

I have used the following general expression to test my expressions:

=Num((Sum({< DateDispensed = {'>=$(=AddMonths('2015-09-30', -12))<=$(=Date('2015-08-31'))'} >} [MedAid Amount] + [Shortfall])), '$##,##0.00')

which gives the result below:

To check whether this is correct i used the following set analysis:

=Num((Sum ({$<$(vSetRolling12)>}  [MedAid Amount] + [Shortfall])), '$##,##0.00')

where vSetRolling12 variable is:

PeriodID = {">=$(=Max(PeriodID) - 11)<=$(=Max(PeriodID))"},

Year = ,

Quarter = ,

Period = ,

Month =

which gives the following result when i select '2015' as the year and 'Aug' as the month from my calendar:

The results are different, what could be the cause for this?

Thanking you.

[jonathandienst]

The set expression is syntactically OK. Without knowing your data model and the format of fields such as PeriodID, its hard to day what is wrong. Date Dispensed may not align with PeriodID, so you are not comparing like with like, or PeriodID may not be a proper sequence number.

I suggest that you post your qvw document or a representative sample.

[Anonymous]

Dear Jonathan

Thank you very much, most appreciated.

Below is my calendar script, hope this will help.  My qvw document is a very large file, how do i get a representative sample?

QuartersMap:
MAPPING LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AUTOGENERATE (12);

Temp:
LOAD
min(Date(Floor([DateDispensed]))) as minDate,
max(Date(Floor([DateDispensed]))) as maxDate
Resident [Main Data];

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD DISTINCT
$(varMinDate) + IterNo()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load *,
AutoNumber(Year & Quarter, 'QuarterID')          as [QuarterID],
AutoNumber(Period, 'PeriodID')                           as [PeriodID],
AutoNumber(Month, 'MonthID')                                    as [MonthID],
AutoNumber(Week, 'WeekID')                                      as [WeekID]
//     AutoNumber(Year & Week, 'weekYearSeq')                   as  [weekYearSeq]
        ;
LOAD DISTINCT
Date(Floor(TempDate)) as [DateDispensed],
Year(TempDate) * 100 + Month(TempDate)            as [Period],
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
YearToDate(TempDate)*-1 as CurYTDFlag,
YearToDate(TempDate, -1)*-1 as LastYTDFlag,
InYear(TempDate, MonthStart($(varMaxDate)),-1) as RC12,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(Weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
Time(Frac(Timestamp#(Tempdate, 'M/D/YYYY hh:mm'))) as TimeDispensed
Resident TempCalendar
Order By TempDate asc;
DROP Table TempCalendar;

Regards

[jonathandienst]

A couple of comments on your calendar. The logic looks sound, although I would do some things differently.

• I am not sure if the Order By carries through a preceding load. The Autonumber method is dependent on the order being correct.
• Month() is a dual function, so it already has a numerical form, so the Autonumber for MonthID is redundant.
• You need PeriodID as sequence. I normally use Year*12 + Month to get a month/period sequence.
• Here is an update:

Load *,

AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID],

AutoNumber(Week, 'WeekID') as [WeekID]

;

LOAD DISTINCT

  Date(Floor(TempDate)) as [DateDispensed],

  Year(TempDate) as Year,

  Month(TempDate) as Month, // dual

  Day(TempDate) as Day, // dual

  Week(TempDate) as Week // dual

  Dual(Year(TempDate) * 100 + Month(TempDate), Year(TempDate) * 12 + Month(TempDate)) as [Period], //dual

  ...

Now use Period instead of PeriodID and Month instead of MonthID. They will be interpreted as numbers or as text, depending on the context. Max(Period) and Max(Period) - 6 will be treated numerically.

I can't see anything else that is wrong and could account for the issue.

[Oleg_Troyansky · Accepted solution]

Christopher - it's REALLY hard to troubleshoot without having access to the actual data, sorry! Too many things can possibly go wrong. For once - how are the fields [MedAid Amount] + [Shortfall] stored? Could there be any null or missing values? Could there by any duplication of data? Any other issues?

Jonathan - in Christopher's defense, using Autonumber here is a clever technique to obtain a unique month number as a sequential integer that crosses multiple years (unlike the numeric part of the Month field that always runs from 1 to 12). However, I share your concern that the order of the load may not persist through the preceding load.

In my practice, I use a more deterministic calculation of the PeriodID:

Year*12 + Month

This calculation also generates a sequential integer number, only not starting with 1. It still serves the purpose of subtracting rolling 12 months, for example.

If I were to bet on the source of the issue here, I'd probably bet on this one - the calculation of the field PeriodID. Try my formula and see if it helps.

cheers,

Oleg Troyansky

[Anonymous]

Thank you very much, will try it out

[Anonymous]

Thanking you, will give it a try and advise accordingly