Previous Week measure from a already existing measure.

[DilligentChickenSoup]

Hi, 

I am working on a measure where I want to calculate a preexisting measures value for the previous week. I’m running into challenges with how to properly make it. 

So say I have a measure named XYZ, and it is defined as: (Some of the things are rewritten, since it confidential like the brand and channel)

text(
Round(
(
(
count({<CHANNEL={'Channel'}, BRAND={'Brand'}, POTENTIAL={'REALIZED POTENTIAL'},
LastDecisionDate_after={"<=$(=Date(Max(interaction_date), 'DD-MM-YYYY'))"},
LastDecisionDate_after={">=$(=Date(Min(interaction_date), 'DD-MM-YYYY'))"}>}
distinct CUSTOMERID)
)
/ (potential * target)
) * 100
, 1)

) & ' %'

I now want to make another measure that is called XYZ_Previous_Week, which as the name implies, just gives me the XYZ measures value from the previous week. 

I have tried different approaches. One of the approaches is that I tried making a new column called: interaction_date_previous_week which was defined as:
DATE(INTERACTION_DATE - 7, 'DD-MM-YYYY') as interaction_date_previous_week.
I then tried to make the  measure called XYZ_Previous_Week which was defined as:
text(
Round(
(
(
count({<CHANNEL={'Channel'}, BRAND={'Brand'}, POTENTIAL={'REALIZED POTENTIAL'},
LastDecisionDate_after={"<=$(=Date(Max(interaction_date_previous_week), 'DD-MM-YYYY'))"},
LastDecisionDate_after={">=$(=Date(Min(interaction_date_previous_week), 'DD-MM-YYYY'))"}>}
distinct CUSTOMERID)
)
/ (potential * target)
) * 100
, 1)

) & ' %'

This XYZ_Previous_Week doesnt work as intented. 

I was hoping someone in the Qlik Community that can help me find them mistake I have made or help me in the right direction.

[MatheusC]

Hi, @DilligentChickenSoup 

maybe this:

yourfieldDate={">=$(=Date(WeekStart(max(yourfieldDate))-7))<=$(=Date(Weekend(max(yourfieldDate))-7))"

or

yourfieldDate={">=$(=Date(WeekStart(today())-7))<=$(=Date(Weekend(today())-7))"}

- Regards, Matheus

[DilligentChickenSoup]

Hi @MatheusC, 

Thank you for the fast response. I have now tried to implement the two different approaches. 

The first:

text(
Round(
(
count({
<CHANNEL={'Channel'},
BRAND={'Brand'},
POTENTIAL={'REALIZED POTENTIAL'},
LastDecisionDate_after={">=$(=Date(WeekStart(Max(interaction_date))-7))<=$(=Date(Weekend(Max(interaction_date))-7))"}
>} distinct CUSTOMERID
)
/ (inbound_potential * inbound_target)
) * 100, 1
)
) & ' %'

And then the second one:
text(
Round(
(
count({
<CHANNEL={'Channel'},
BRAND={'Brand'},
POTENTIAL={'REALIZED POTENTIAL'},
LastDecisionDate_after={">=$(=Date(WeekStart(Today())-7))<=$(=Date(Weekend(Today())-7))"}
>} distinct CUSTOMERID
)
/ (inbound_potential * inbound_target)
) * 100, 1
)
) & ' %'

Both of them doesnt seem to give the correct answer that I am looking for. If you have any other approaches, or if I have misinterpreted your approaches. Then please let me know 😄

[marcus_sommer]

I suggest to simplify at first the testing - because to find the right way to reference to the wanted dates no further conditions/multiplication/formatting and so on are needed. Further comparing a numerical value like a date against another date per >= or <= happens on a numerical way which means the date() formatting isn't necessary (by comparing = the format is important because the string-representation is taken).

This means something like this should be working (as far as -n is applicable against min and max):

DateX={">=$(=min(DateY)-7)<=$(=max(DateY)-7)"}

Important by such approaches is further that no DateX respectively the derived week from it is part of the object because the set analysis condition may overwrite any selection state but won't overwrite the associations to the dimension-values. Therefore just start with a new table with channel as single dimension and then use two expressions - one with the current week reference and the other with the previous one. If this worked like expected you could increase the dimensions/conditions step by step.

[Kushal_Chawda]

@DilligentChickenSoup  try below. last parameter in weekstart function indicates first day of the week. ) means Monday. You can change it accordingly.

LastDecisionDate_after={">=$(=WeekStart(Max(interaction_date),-1,0))<=$(=Weekend(Max(interaction_date),-1,0))"}

[DilligentChickenSoup]

Hi @Kushal_Chawda 

I have tried the approach that you mentioned:
text(
Round(
(
count({
<CHANNEL={'channel'},
BRAND={'brand'},
POTENTIAL={'REALIZED POTENTIAL'},
LastDecisionDate_after={">=$(=WeekStart(Max(interaction_date), -1, 0))<=$(=Weekend(Max(interaction_date), -1, 0))"}
>} distinct CUSTOMERID
)
/ (inbound_potential * inbound_target)
) * 100, 1
)
) & ' %'

It doesnt seem to work. If I have misunderstood your approach then please let me know. 🙂

[DilligentChickenSoup]

Hi @marcus_sommer 

It sounds like a good idea. I will try with one channel and if it works,  I will just increase the number of channels. I will let you know if this approach yields fruit. 🙂