Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

parks399
New Contributor II

Set Analysis divide monthly value into daily value

I am having trouble creating a chart that will track percent to plan by day.  The plan is loaded in a script with fiscal day equal to the last fiscal day of the month.  I am creating combo chart with the Fiscal_Days as dimension.  Using the below as an example, I am trying to create a Plan by day estimate, but i can not get the calculation with Plan in it to show except on day 20 where it is loaded.  

My last failed attempt is : (sum({$<{Fiscal_Days=,Period={'Jan'}>}Plan))/max(Fiscal_Days)

This only appears on Day 20.


For this example, I would like to see the daily plan of $1000 in the graph referencing the above formula. 

Any thoughts??

 

PeriodYearFiscal_DaysSalesPlan
Jan201811000
Jan201821010
Jan201831020
Jan201841030
Jan201851040
Jan201861050
Jan201871060
Jan201881070
Jan201891080
Jan2018101090
Jan2018111100
Jan2018121110
Jan2018131120
Jan2018141130
Jan2018151140
Jan2018161150
Jan2018171160
Jan2018181170
Jan2018191180
Jan201820119020000
6 Replies

Re: Set Analysis divide monthly value into daily value

What is the expected output you are looking for?

Re: Set Analysis divide monthly value into daily value

Maybe using TOTAL qualifier to ignore dimension values? like:

(sum(TOTAL <Year, Period> {$<{Fiscal_Days=,Period={'Jan'}>}Plan))/max(Fiscal_Days)

parks399
New Contributor II

Re: Set Analysis divide monthly value into daily value

I'm looking for something like this.  Used excel to show the math easier.  Basically, I want to show all the fiscal days as dimension and bar for Sales and line for calculation of plan by day.  This seems simple, but the coding is not straight forward.  I appreciate the help.

error3.JPG

Re: Set Analysis divide monthly value into daily value

Then try what rubenmarin gave above

parks399
New Contributor II

Re: Set Analysis divide monthly value into daily value

I think this works, but i don't understand why.  Can you explain 'TOTAL' and why it needed to reference Year & Period?

Re: Set Analysis divide monthly value into daily value

TOTAL is used to ignore dimension values, the fields between <> are the dimensions you don't wan't to ignore, in this case year and month.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/define-ag...

If you look at the examples in help link, set analysis can be used to ignore selections, but it doesn't ignores dimension values, TOTAL does that.

Community Browser