Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

What is the expected output you are looking for?

rubenmarin

Maybe using TOTAL qualifier to ignore dimension values? like:

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

Anonymous
Not applicable
Author

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

sunny_talwar

Then try what rubenmarin gave above

Anonymous
Not applicable
Author

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

rubenmarin

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.