Calculating 12 month buyers

[#1]

Hi Forum, newbie here.  This Qlikview stuff is great, but have hit a road block and hoping someone can help me.  I'm trying to calculate unique buyers on a monthly basis.  That part is easy.  I'm also trying to calculate unique buyers 12 months after that, and that's the trouble I'm having:

I'm using this in my set analysis:

Count({$<FiscalMonth={">$(=Max(FiscalMonth)) <=$(=Max(FiscalMonth) + 12)"}>}DISTINCT CustomerNumber)

However I don't think it's right.  I think I should be using order date as adding 12 months to the fiscal month won't take me into the next year.  Also the above calculation does work to some extent only if I make a selection on fiscal month.  If no fiscal month is selected then I get the above result - all 0s.

I'd appreciate any help, on constructing the calculation and also to them to work even if no selection is made.

Thanks,

Jim

[Anonymous]

Not a solution but explanation:

Set analysis calculates data over the whole data set (selected in your case), not the chart row.  So, when you select one month, the max month is this one that you selected.  If you don't select anything, the max month is the very last in your data, and apparently no any data after that - hence 0.
I'd recommend to pre-calculate "after 12 months" in the script.  Or maybe use a data island for the chart dimension, so you can use set analysis in expressions.

[maternmi]

Hi James,

please take a look to the attachment. I think page 11 will be helpful for you.

BR

Michael

[#4]

Thanks Michael, that does explain the results I'm getting.

Maternmi, thanks for the attachment.  That's a great resource!

Jim

[mukesh24]

Hi,

  To solve these use set analysis

Simply create variable as:-

vMaxYear = max(Year)

vMaxMonth=max({<Year = {"$(vMaxYear)"}>}Month)

vNextMonth =  month(addmonths(makedate($(vMaxYear), $(vMaxMonth)),12))

vNextYear = Year(addmonths(makedate($(vMaxYear), $(vMaxMonth)),12))

Use above variables in expression using set analysis..

For Current Month selection

count({<Year = {"$(vMaxYear)"},Month={"$(vMaxMonth)"}>}Distinct CustomerNumber)

for last month

count({<Year = {"$(vNextYear)"},Month={"$(vNextMonth)"},Year=,Month=>}Distinct CustomerNumber)

Regards,

Mukesh Chaudhari