topic Expression help in QlikView

Expression help

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I've taken on a few dashboards and I am trying to understand some of the expressions. Would anyone be able to explain in simple terms what these expressions are actually doing?

YTD Sales:

sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)

Average intake:

sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)/(date#(today())-date#($(vYrStart)))

Predicted YTD:

=(sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)/(date#(today())-date#($(vYrStart))))*365

Av intake req:

(sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}TARGET)-sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES))/(365 - (date#(today())-date#($(vYrStart))))

Target:

=sum({<FINMONTH = , FINYEAR = {'$(=max(FINYEAR))'}, CAL_MONTH_STR=>}TARGET)

TIA

Re: Expression help

marcus_malinow — Wed, 06 Sep 2017 10:18:30 GMT

YTD Sales:

sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)

This is a Sum of SALES with the following criteria

FINYEAR is the maximum FINYEAR (taking into account your current selections)

FINMONTH is greater than max(FINMONTH) minus max FINMONTH (which is completely pointless as it will always be zero!) and is less than 12

Average intake:

sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)/(date#(today())-date#($(vYrStart)))

Is YTD Sales, but divided by today() minus the variable vYrStart, so I would expect thias to return an average daily figure here

Predicted YTD:

=(sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)/(date#(today())-date#($(vYrStart))))*365

This is the Average intage multiplied by 365 to project a figure for the whole year

Av intake req:

This the difference between YTD Sales and Predicted YTD

Target:

=sum({<FINMONTH = , FINYEAR = {'$(=max(FINYEAR))'}, CAL_MONTH_STR=>}TARGET)

this is simply a SUM of TARGET for the current FNYEAR

Re: Expression help

Wed, 06 Sep 2017 10:39:36 GMT

Marcus thank you!

Are you able to advise how I can get a networkdays calculation for the average intake? This will then allow me to do more accurate reporting as orders are not received on weekends etc.

Re: Expression help

marcus_malinow — Wed, 06 Sep 2017 10:41:36 GMT

I'm not sure here but you *may* have an issue with your Average Intake, Predicted YTD, and Av Intake Req if you were to select a prior FINYEAR.

YTD Sales will correctly return the result for the FINYEAR selected, however your divisor may be incorrect,

This is because of the (date#(today())-date#($(vYrStart)) calculation. today() will evaluate to 6th September 2017, and I presume vYrStart will evaluate to the year start of the selected FINYEAR.

Re: Expression help

marcus_malinow — Wed, 06 Sep 2017 10:46:05 GMT

ok, based on the above expression, which as noted I believe is flawed!

Average Intake would be something like

sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH= >}SALES)/

networkdays(date#($(vYrStart)), (date#(today()))

I have simplified the FINMONTH part, as I am assuming that you have no FINMONTHs below zero or above 12, so basically we can ignore selections in that field

Also I have divided by Networkdays between today and whatever is being returned by your vYrStart variable

Re: Expression help

Wed, 06 Sep 2017 13:54:17 GMT

perfect!

I've managed to create a few variables and it's now working much better thanks for your help.