topic Re: Set analysis for rolling twelve months in QlikView

Set analysis for rolling twelve months

Fri, 08 Jun 2012 09:51:34 GMT

Hello,

I've problems to get my set analysis working properly good. Se example below:

count({$<PeriodCounter={">$(#=max(PeriodCounter)-13) <=$(#=max(PeriodCounter)-1)"}, Month=, Year=, Quarter=>}CustomerID)

So when I've not done any selections everything looks good, see attached picture Chart1. But when I do a selection in the chart for one month the results get really wrong, see attached picture Chart2.

Any one that have a good solution?

Regards

Re: Set analysis for rolling twelve months

jonathandienst — Fri, 08 Jun 2012 10:41:36 GMT

It seems that there is one or more fields that are linked to the date that still need to be excluded (selection ignored) in addition to Month, Year and Quarter.

Hope that helps

Jonathan

Re: Set analysis for rolling twelve months

christian77 — Fri, 08 Jun 2012 10:44:13 GMT

Hi:

I've done something similar before. QV dimensionates metrics. If you add several months in a metric and then dimensionate the metric by the month, it desaggregate again your adding by the month. You can show it in a text box but not in a chart monthly dimensionated.

Solutions:

Bring that calculation on the script by looping and groupping those months.

(if they are many subgroups it'll take longer)

Create a calendar in wich every new period encapsulates 12 periods of the regular calendar.

(Creates 12 records per each record of the original calendar)

Make it in a series of text boxes.

Calculating TAM and BeforeTAM (months -13 to -24) is usually required by clients. I think it's necesary to come up with a suitable solution and post it in this community.

Re: Set analysis for rolling twelve months

Fri, 08 Jun 2012 12:25:32 GMT

When I use the following set analysis it is working good but I can´t change when the rolling twelve month should starts, it always starts with the last month. I thought that I should use like -1 where the xx is but it doesn´t matter what I do.

count({$<PeriodCounter={"<=$(#=max(PeriodCounter)xx)"}, PeriodCounter={">=$(#=max(PeriodCounter)-12)"}, Month=, Year=, Quarter=>} CustomerID)

Any one that have any suggestions?