Subtracting two variables.

[#1]

Hi! I am new in Qlikview and I am having some troubles with variables.

I need to calculate the diference between the same variable evaluated in two differents dates. 

I think it would be something like: f(x) - f(x-1)

where f is the variable

         x= Year($(vMaxDate))

         x-1= =Year($(vMaxDate), -1)

This is the variable (which is also a division):

=$(vUsersLoggingCalls_Level2(Evolution ,Evolution))/$(vActiveProfileEmployees_Level2(Evolution ,Evolution))

Any idea?

[rubenmarin]

Hi esther, vUsersLoggingCalls_Level2 and vActiveProfileEmployees_Level2 seems parametrized variables, I think the new conditions should be inserted in those variables, it can be done adding a 3rd parameter for the year or creating 2 different variables: one for max year and another for the previous.

In any case I will need to check what those variables contains because the modification should be done in those variables ( vUsersLoggingCalls_Level2 and vActiveProfileEmployees_Level2).

Regards.

[Gysbert_Wassenaar]

This isn't correct: =Year($(vMaxDate), -1). If you want to subtract 1 from the year of the max date you use: =Year($(vMaxDate))-1. If you want the year of the day before the max date you use =Year($(vMaxDate) -1)

[amit_saini]

Hi,

Could be something like below:

=sum(aggr(

sum( {<PRI_CALENDAR_END={">=$(vStartDate)"}, PRI_CALENDAR_START={"<=$(vEndDate)"}>}

Interval( if( PRI_CALENDAR_END > vEndDate, vEndDate, PRI_CALENDAR_END ) - if( PRI_CALENDAR_START < vStartDate, vStartDate, PRI_CALENDAR_START ) )

), SLINIENR) )

Thanks,

AS

[PrashantSangle]

Hi,

If you are using Year() then as Gysbert point out Year() take only 1 parameter not 2.

If this is not your case.

then can you post $(vUsersLoggingCalls_Level2) and $(vActiveProfileEmployees_Level2) definition????

Regards,

[#6]

Hi Ruben, thanks for your answer.

The variables are:

vUsersLoggingCalls_Level2:

Sum({$<$(vAdoptionRangeDate($1,$2,$3)),[Employee Position Type]=,[Profile]=,[Territory Nominal F. District]=,[Territory Nominal F. Position]=, [Employee]=, [Profile]={'Sales','Medical','Marketing','MarketAccess'}, [QlikView Login Application]=, [Feed Record Type]=,[Chatter Origin]=, Origin = {'Employee History'}, [Employee EMEA] = {'No'} ,[Employee Is Active] = {'Yes'}>}[Counter])

vActiveProfileEmployees_Level2:

Sum({$<$(vAdoptionRangeDate($1,$2)),[Employee Position Type]=, [Profile]={'Sales','Medical','Marketing','MarketAccess'},[Territory Nominal F. District]=,[Territory Nominal F. Position]=,[Employee]=,[Employee Is Active] = {'Yes'}, Origin = {'Call'}, [Growth YearMonth]= {">=$(vMinYearMonth) <=$(vMaxYearMonth)"}, [Employee YearMonth Id] = {"=Sum({$<MonthYear=,[Employee Position Type]=,[Territory Nominal F. District]=,[Profile]=,[Territory Nominal F. Position]=,[Employee]=,Month=,Year=,Origin = {'Call'}>}[Parent Call Counter]) >= 1"}>}[Employee Counter])

[#7]

vAdoptionRangeDate($1,$2,$3):

$(=If('$1' = 'KPI', '$(vAdoptionTemporalSelection),[Growth MonthYear]=,[Date]={">=$(=$(vAdoptionStartRollingYear(KPI)))<=$(=$(vMaxDate))"}',

If('$1' = 'Cluster', '[Country]=,[Region]=,[Cluster]=p(),$(vAdoptionTemporalSelection),[Date]={">=$(vAdoptionMinDate($2))<=$(vAdoptionMaxDate($2))"}',

  If('$1' = 'EMEA', '[Country]=,[Cluster]=,[Region]=,$(vAdoptionTemporalSelection),[Date]={">=$(vAdoptionMinDate($2))<=$(vAdoptionMaxDate($2))"}',

   If('$1' = 'Evolution','$(vAdoptionTemporalSelection),[Growth Calendar Difference]={"0"},[Date]={">=$(=$(vAdoptionStartRollingYear(KPI)))<=$(=$(vMaxDate))"}',

    If('$1' = 'Region','[Territory Nominal Function]=,[Territory Nominal Franchise]=,[Territory Nominal F. District]=,[Territory Nominal F. Position]=,[Employee]=,$(vAdoptionResetRegion($3)),$(vAdoptionTemporalSelection),[Date]={">=$(vAdoptionMinDate($2))<=$(vAdoptionMaxDate($2))"}',

      If('$1' = 'L5','[Territory Nominal F. District]=,[Territory Nominal F. Position]=,[Employee]=,$(vAdoptionResetHighTerritory),$(vAdoptionTemporalSelection),[Date]={">=$(vAdoptionMinDate($2))<=$(vAdoptionMaxDate($2))"}',

        '$(vAdoptionTemporalSelection),[Date]={">=$(vAdoptionMinDate($2))<=$(vAdoptionMaxDate($2))"}')))))))

[#8]

I dont really think is a good idea to try to modify those variables...

I was looking for a string or function that allows me to subtract both results, I can evaluate the variables if I select different values for the field YearMonth. The issue is calculating the diference.

[#9]

Hi Max, thanks for you answer.

The variables are huge, I do not really think I should change them.

If a select diffetent values in the field YearMonth, the varibles are evaluted for the each value respectively.

Then, I was more looking for a way to store/save the results and substract them. May be like a function, string or set analysis. 

[Gysbert_Wassenaar]

(A-B) and Rangesum( A, -B) will subtract B from A.