Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
LisaG
Contributor II
Contributor II

Budget per production day per dimensions

Hi,

I have two tables (attached you can find them),

  • the fact table with the budget figures based on
    • month and year
    • a generated date (01&'month'&'year')
    • material
    • client
    • plant
  • the calendar with
    • date, month, year
    • the production days per day (1 for production day, 0 is not a production day). 

They are connected with the key "date".

My goal is to sum up the budget for the year and divide it with the production days, so I get a new budget based on the production days.

With this formula I get the right figures per month:

Sum({$<[COPA.Material group 4_MVGR4_]-={'Q99'},plant={'CH30'}>}total[BU quantity])
/
sum(total[production days])
*
sum([production days])

But if I want to use filter like material or client the figures are wrong. You have any ideas to solve my problem?

Thanks a lot,
Lisa

Labels (3)
2 Replies
rogerpegler
Creator II
Creator II

The data model doesn't match your desired logic.  When you drill down on something like client it's reducing the data set to only the first of the month and the other days in the month with their production days are ignored. 

One approach could be to use aggr and {1} as your set analysis in expressions, but this would be fiddly and unique per chart.

A simpler approach would be to rollup the calendar to being one record per month with the number of productions days being aggregated.

LisaG
Contributor II
Contributor II
Author

I found a solution: add e.g. month= to the measure.