Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I wanted to report data from two tables in one application, one table is weekly revenue, the other table is today's revenue. Both tables include same dimension variables (e.g., date, product category, location, status).
I have two KPI charts, one always showing today's sum revenue, the other showing a specific week's sum revenue (the specific week is determined by a filter pane to select a specific date). I also have other filters (e.g., product category, location, status) which can filter both today and a specific week's revenue.
No matter whether I concat two tables or create synthetic keys, when I select a specific date for the week's sum revenue KPI, it changes today's sum revenue as well.
I thought about using alternative states, but today and week revenue are filtered by some common filters (e.g., product category, location, status), so I can't completely separate them.
I'm wondering if there is any solution here.
Did you try a set expression on sum of today's revenue? Something like sum({<date_filter=>}revenue)
Your expression for Today should be fixated to the MAx date as below
Todays Revenue = sum({<Datefield={"$(=Date(Max({<Datefield=>}Datefield)))"}>}Revenue)
I think my problem is, for example, if I select 02/01/2024 for weekly KPI chart, it filters weekly data. Then on 02/01/2024, there may be only product type a, location b, etc. Today's data then gets automatically filtered because product type and location are the synthetic/composite keys between weekly and today data. That means today KPI chart only includes data that with product a and location b. I hope this make sense!