topic Re: Help deciphering an expression or issue in QlikView

Help deciphering an expression or issue

Anonymous — Fri, 18 Sep 2015 21:11:08 GMT

i apologize but this discussion will sound pretty basic.. but i need some help trying to figure out an expression..

this expression was done by someone else and im having issues with it and i cant seem to figure out whats going on..

in the sheet im getting no data for one object (1 calculated dimension and 1 true dimension).. i get nothing all blanks

but another object with the same expression.. (2 true dimensions is used) ... i get data and the math is working

i just need some help trying to decipher or break down the embedded ifs with the sums and stuff

= if ((if(SecondaryDimensionality()=0, rangesum(-first(sum({$<season=,season=[Previous]>}skuCount)),last(sum({$<season=,season=[Previous]>}skuCount))), sum({$<season=,season=[Previous]>}skuCount))

=0 and (if(SecondaryDimensionality()=0, rangesum(-first(sum(skuCount)),last(sum(skuCount))), sum(skuCount))>0)),1,

(if(SecondaryDimensionality()=0, rangesum(-first(sum(skuCount)),last(sum(skuCount))), sum(skuCount))-

if(SecondaryDimensionality()=0, rangesum(-first(sum({$<season=,season=[Previous]>}skuCount)),last(sum({$<season=,season=[Previous]>}skuCount))), sum({$<season=,season=[Previous]>}skuCount)))/

if(SecondaryDimensionality()=0, rangesum(-first(sum({$<season=,season=[Previous]>}skuCount)),last(sum({$<season=,season=[Previous]>}skuCount))), sum({$<season=,season=[Previous]>}skuCount)))

any direction will be great

thanks

Re: Help deciphering an expression or issue

jolivares — Fri, 18 Sep 2015 21:22:03 GMT

Try to post the result chart to get a better view of what the expression y calculating, because in this case I see that is a pivot table and is calculating a rangesum() depending the value of the dimensionality(). This is use to show totals in case of tha chart is collapse or no.

Re: Help deciphering an expression or issue

evan_kurowski — Sun, 20 Sep 2015 00:12:07 GMT

Hello Jackie,

PARAPHRASE (Metric: Seasonal % change in Inventory):

= IF (
$(vLastSeasonsInventory)=0 AND $(vThisSeasonsInventory)>0,
1
, //else
$(vThisSeasonsInventory)/$(vLastSeasonsInventory) - 1
)

CALCULATION METHODOLOGY for tabulating seasonal change in item inventory volume:

WHEN either not using pivot table or there are no horizontal dimensions in the pivot table:

if(SecondaryDimensionality()=0

Use inventory calculation method A:

Calculate a season’s change in inventory by summing up all the activity and subtracting the starting point from the ending point
rangesum(-first(sum(skuCount)),last(sum(skuCount)))

ELSE

Use inventory calculation method B:

Calculate a season’s change in inventory by summing up the activity at the row level

sum(skuCount))

CALCULATION (% Ratio for seasonal change in Inventory):

IF last season's inventory was nothing

(
if(SecondaryDimensionality()=0,
rangesum(-first(sum({$<season={$(=[Previous])}>} skuCount)),last(sum({$<season={$(=[Previous])}>}skuCount))),
sum({$<season={$(=[Previous])}>}skuCount))
)=0

AND this season's inventory was something,

(if(SecondaryDimensionality()=0, rangesum(-first(sum(skuCount)),last(sum(skuCount))), sum(skuCount)))>0

THEN //all inventory tabulated this season is completely new inventory

The ratio of inventory increase is 1 (100%)

ELSE //there was inventory on-hand at the start of this season

Get the final tally of this season's inventory

(if(SecondaryDimensionality()=0, rangesum(-first(sum(skuCount)),last(sum(skuCount))), sum(skuCount))

Subtract the final tally of last season's inventory

if(SecondaryDimensionality()=0,
rangesum(-first(sum({$<season={$(=[Previous])}>}skuCount)),last(sum({$<season={$(=[Previous])}>}skuCount)),
sum({$<season={$(=[Previous])}>}skuCount))))

take the calculated volume change in inventory and divide it by the inventory on hand at the end of last season, to calculate the % change in inventory over the last season

if(SecondaryDimensionality()=0, rangesum(-first(sum({$<season={$(=[Previous])}>}skuCount)),last(sum({$<season={$(=[Previous])}>}skuCount))), sum({$<season={$(=[Previous])}>}skuCount)))

ENCAPSULATED INTO VARIABLES:

vThisSeasonsInventory = if(SecondaryDimensionality()=0, rangesum(-first(sum(skuCount)),last(sum(skuCount))), sum(skuCount))

vLastSeasonsInventory = if(SecondaryDimensionality()=0, rangesum(-first(sum({$<season={$(=[Previous])}>}skuCount)),last(sum({$<season={$(=[Previous])}>}skuCount))), sum({$<season={$(=[Previous])}>}skuCount))

FINAL EXPRESSION:

= IF (
$(vLastSeasonsInventory)=0 AND $(vThisSeasonsInventory)>0,
1
, //else
$(vThisSeasonsInventory)/$(vLastSeasonsInventory) - 1
)

Re: Help deciphering an expression or issue

Anonymous — Mon, 21 Sep 2015 19:10:00 GMT

hi evan

this is awesome exactly what i was trying to figure out..

i kinda got some of what u are saying before.. but the one thing that is confusing me.. is the rangesum(-first(sum(skuCount)

the negative in front of the first does it make the whole first range number a negative number.. and then add on the end range range positive number?

i am confused to that negative in front

thanks

Re: Help deciphering an expression or issue

evan_kurowski — Tue, 22 Sep 2015 02:17:14 GMT

Yup, you got it, it is just a negation of the first entry in the series. Presumably the sort is chronological, so they're saying subtract the start from the finish, to get the change.

As an example, say the first row shows we started with 400, the last row ended with 1000.
1000 - 400 = 600 gained for the season.

600/400 = 150% increase in seasonal inventory