topic Re: Subtracting two variables. in QlikView

Subtracting two variables.

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Subtracting two variables.

rubenmarin — Wed, 13 Jul 2016 10:04:27 GMT

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.

Re: Subtracting two variables.

Gysbert_Wassenaar — Wed, 13 Jul 2016 10:06:31 GMT

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)

Re: Subtracting two variables.

amit_saini — Wed, 13 Jul 2016 10:07:57 GMT

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,

Re: Subtracting two variables.

PrashantSangle — Wed, 13 Jul 2016 10:20:28 GMT

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,

Re: Subtracting two variables.

Wed, 13 Jul 2016 10:50:26 GMT

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])

Re: Subtracting two variables.

Wed, 13 Jul 2016 10:52:27 GMT

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))"}')))))))

Re: Subtracting two variables.

Wed, 13 Jul 2016 11:00:19 GMT

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.

Re: Subtracting two variables.

Wed, 13 Jul 2016 11:06:51 GMT

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.

Re: Subtracting two variables.

Gysbert_Wassenaar — Wed, 13 Jul 2016 11:30:44 GMT

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

Re: Subtracting two variables.

rubenmarin — Wed, 13 Jul 2016 11:44:29 GMT

Hi Esther, this variable has a lot of set analysis fixing the dates (one for each of the values if the 1st paramater), I think the best way is duplicating variables, one as it is for the current year (or maybe also needs a fix to keep only one year). The other one for the past year, and with the variables adapted to query the past year

$(=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))"}',

'$(vAdoptionTemporalSelection),[Date]={">=$(vAdoptionMinDate($2))<=$(vAdoptionMaxDate($2))"}')))))))Re: Subtracting two variables.

Re: Subtracting two variables.

rubenmarin — Wed, 13 Jul 2016 12:02:46 GMT

To expand my answer: yes, it's difficult (and time-consuming) to modify this variables but maybe there is a possibility to add a paremeter to the vAdoptionMinDate, vAdoptionMaxDate and vAdoptionStartRollingYear variables (and by extension to vAdoptionRangeDate, vUsersLoggingCalls_Level2 and vActiveProfileEmployees_Level2; these ones should receive the parameter to pass it to the variables this variables contains).

Also changing $(vMaxDate) to a new parametrized variable that, using the same parameter as the previous variables, returns the Max date for Year($(vMaxDate)) or Year($(vMaxDate))-1, based on the parameter it receives.

Re: Subtracting two variables.

Wed, 13 Jul 2016 12:24:47 GMT

Thanks Ruben, I will try that

Re: Subtracting two variables.

rubenmarin — Wed, 13 Jul 2016 12:35:41 GMT

Hope it will be possible to do it, I still don't know the contains of some of the variables used (and I will need some sample to test before been able give a confirmed answer) but if you achieved to go so far with these variables I'm sure you have enough knowledge to modify those.

Also a 2nd opinion would be nice before starting this difficult task, anyone in this thread sees another way for doing this?