set analysis with date range and possible values P()

danmartins — Fri, 04 Sep 2015 01:12:00 GMT

Hello everyone,

Here's the challenge I'm facing:

I want to calculate the performance of marketing actions. To achieve that I am comparing the sales during the action period against the same period in the previous month and previous year.

I created one register for each day of campaign and it is linked to the fact table by date, store and product.

This is the expression to calculate the sales within the action period:

sum({$<Date={">=$(=num(min([Actions.BeginDate])))<=$(num(max([Actions.EndDate])))"},

Store=P(Actions.Store),

Product=P(Actions.Product)>} Sales)

This expression will also give me the correct result if there is more than one action selected. For instance: if products A and B are marketed in April and products C and D in May, it gives me the sales of A+B in April and C+D in May.

I tried to create similar expressions to calculate the sales of different periods:

sum({$<Date={">=$(=num(AddMonths(min([Actions.BeginDate]),-1)))<=$(num(AddMonths(max([Actions.EndDate]),-1)))"},

Store=P(Actions.Store),

Product=P(Actions.Product)>} Sales)

But this will give me the same result as the first expression and I have no idea why..

Then I created comparison date fields in the script for each action date, i.e. if Date=14/02/2015, there is a field Date_LastMonth=14/01/2015 and another for last year. Then I used the following expression:

sum({1<Date=P(Actions.Date_LastYear),

Store=P(Actions.Store),

Product=P(Actions.Product)>} Sales)

This formula only works if I use 1, to represent the full set of records. If I use $ it will give me zero (would someone explain why?).

Another effect is that it only works if one action is selected - or else if they share the same store and products. If they don't, as in the example above, the expression gives the result of A+B+C+D in April and May.

The same happens to:

(sum({1<Ano={'$(vMaxYear)'},

Mês={'$(vPriorMonth)'},

Dia={">=$(=day([Actions.BeginDate]))<=$(=day(MonthEnd([Actions.EndDate])))"},

Date=,

Store=P(Actions.Store),

Product=P(Actions.Product),

CdCampanha=>} Sales))

I tried to combine these expressions with aggr() using Action as dimension but it also didn't work.

Any help on how I can have the sales of the proper products in the proper stores and date ranges?

If you need any extra information or clarification please let me know.

Cheers,

Dan

topic set analysis with date range and possible values P() in QlikView

set analysis with date range and possible values P()