Select Data using a Boolean from a module with other dimensions

QZ
QZ
in Modeling

Hello,

I have the three following modules:

Pick Year:
Dimensions: Product, Forecast, Time (Year)
Line Item: Select (Boolean)

Employee Sales:
Dimensions: Employee, Market, Time (Year)
Line Item: Sales (Number) - Filled with a flat Sales module (Flat Sales Module.Sales Amount[sum: Employee, sum: Market, sum: Year] (simplified)

Sales for Picked Years:
Dimensions: Employee, Market, Time (Year)
Line Item: Sales if Picked Year (Number)

My issue lies with the last line item, Sales if Picked Year. Does anyone know how I could make the selection from "Pick Year" decide whether my Sales are taken from "Employee Sales" and shown in "Sales for Picked Years".

The end goal will be to have another module where I remove Market and Time as dimensions and only aggregate the data per Product and Forecast (I'm picking an Employee for each combination of Product/Forecast in another module). The Sales for Picked Years module will be an intermediate module there.

For a similar issue I added Forecast as a dimension in the module equivalent to "Sales for Picked Years" however this takes space in the model, and I would like to avoid this (and maybe replicate a potential solution on my previous work).

Thanks a lot !

Answers

  • BrunoRodriguez
    BrunoRodriguez

    Hello @QZ ,

    As I understand it, you have 3 modules:

    1. Driver: Product, Forecast, Time (Year)
    2. Data: Employee, Market, Time (Year)
    3. Module where the driver defines the data flowing: Employee, Market, Time (Year)

    How are Product and Forecast connected with Employee and Market? If there is no relationship you could have the Driver module dimensioned just by Year.

    Cheers!

    Kind regards,

    Bruno Rodriguez

  • QZ
    QZ
    edited May 2023

    Hello Bruno,

    In another Module I pick an Employee per combination of Product and Forecast like below:

    image.png

    For my use case, I will need to specify the chosen year per Product and Forecast together so I can calculate only the Sales per Employee on the year(s) that I find relevant, please see below:

    image.png

    Unfortunately I have to pick a Year on this level of granularity. I also will have to do the same exercise for Market but if one is solved the other will be as well.

    Hope that clarifies my problem.

    Thanks !

Categories