how to create not a dynamic calculation

Maria_Rozenblit
2 yrs ago
4replies
Answered

Hi,

I have a case where I need to make the next calculation:

In a table with the columns: week | store name | item name | sales

For each item:

In each store, count of weeks where the sales of the item>0 / in each store, count of weeks where total sales>0.

How can I calculate the denominator and make it not dynamic so each store will get the same results? (when I'm adding the "item name" to the rows it's automatically calculating both of the calculations on the item level and the result is always 1).

Thanks

- NPANS
- 2 yrs ago
- Reported - view
The question is unclear. Can you supply a sample dataset and some SQL that you may have written to solve this problem, if you queried a database directly.
- Maria_Rozenblit
- 2 yrs ago
- Reported - view
Hi,

Thank you for the help.

I’m trying to calculate it in the discovery, so the results are changing depends on what I put in the rows.

For exp. When I’m looking on the items I get the right denominator value but for both denominator and numerator.

When I’m adding the stores to the rows I’m getting the right result of the numerator but again for the denominator it’s ta same.

Maybe I’m doing something wrong in the calculations in formulate or it's something i can calculate only on the model and not using formulate and discovery.

In EXCEL I would make a VLOOKUP and bring one of the results from another table.
- NatalieAnne_Botha
- 2 yrs ago
- Reported - view
Hi Maria,

Have you tried selecting ALL the elements for the Items in the denominator?

Here is an example using the sample data. This would fix the calculation to show the same number for all products:
- Maria_Rozenblit
- 2 yrs ago
- Reported - view
Hi Natalie,

thanks for the help.

I tried it., it didn't work.

I'll try to make some changes in the model, maybe it will solve it.