Set expression with dynamic time frame used in aggr functon not working.

[EspenH]

We have created a solution to follow up sales. In the solution we chose a year and one or more months (master dimension). We monitor new customers, lost customers, customers with sales up and customers with sales down between the chosen period and the same period previous year.

All is working except "Lost customers" where we don't have sales (transactions) this year, only pervious year.

In the solution we have created master measures for the calculations with dynamic timeframes, but we struggle with the lost customers. The master measures is used in aggr functions for incorporation three dimensions.

We belive the problem is with the set expressions in the master measures we have crated. We have attached the qvf file which hopefully explains everything.

To all you experts - is there a solution for this?

[marcus_sommer]

Each calculation-result needs a dimension-value against it be could be calculated and displayed - always, there is no exception.

Depending on the data-set, the data-model and the wanted views it might be possible to create appropriate calculated dimensions within the UI but it will always have side-effects to the complexity, performance and usability - and could become quite ugly.

Better as such attempts is usually to populate the missing the data within the data-model and maybe applying further useful stuff, like various flags/counters, for example a descending index and/or the period-offsets between then an/or the period-offsets against today or ...

How to populate a sparsely populated field - Qlik Community - 1470637

[EspenH]

Hi,thank you for feedback.

Do you mean that you can not calculate values on a missing dimension? I my case they previous year? And its possilbe on the aggregation because I use higher granularity in the calculation? 

As I wrote in the case I believe this is a theoretical approach since i then need about 720 000 0000 transactions (20000 X 100 x 30 X 12), but maybe I am wrong? Please elaborate if I have note got this right.

[EspenH]

Sorry, one 0 too mych, but still i high number of transacstions (720 000 000).

[marcus_sommer]

Yes, you will always need a dimension-value to be able to perform a calculation against it. It's not specific for Qlik else a general logical rule and happens also in SQL or Excel.

The usual ways to bypass the problem is not using the transaction-facts else the dimension-tables and/or creating a new calculated table/dimension (data-model respectively UI) respectively combinations of both methods. Many people aren't aware that they do exactly these kind of measurements if they do such stuff in Excel and it seemed to be a so simple task - they do at least one extra step to create the dimension-values and against then the summing or lookup is performed..

But they will cause the above mentioned side-effects whereby they may be in some simpler scenarios the most practicable way to get the needed view.

Nevertheless is populating the missing data als a practically way especially as the commonly targeted ZERO analysis is seldom wanted against any atomic level of orders, products or dates else against higher level like product-groups and years/months which would reduce the number of populated records significantly. Rather slightly higher efforts/run-times/sizes in the data-model will lead to much more performant and usable UI experience.

[EspenH]

Hi

Thanks againg.

I found one solution, I changed the dimension from drill-down Year/month to create separate dimensions for Year and month. The user must then chose what years to compare and the specific months. This gives me the right number with the same set expressions. Do you see any challenges with this?

[marcus_sommer]

Without a deeper look in the data-set/data-model and the various object-views respectively requirements it's not possible to say if there are any further potential challenges. Because of this were my recommendations not very specific to your case else more general to a NULL/ZERO handling - ideally is this kind of task already considered before starting to design the data-model because extending an existing application for new features is more difficult.

[Kushal_Chawda]

@EspenH  What is the logic to get new customers? What is expected values when you select 2024?

[EspenH]

Hi, thanks for replay.

The value which causes trouble are "Lost customers", all the other are good.

If you see the table to the right you see the details, which i get when I also use all the dimensions. In the upper tabel i expect for Kari Lost customers = 2 000 000 and for Peter Lost customers = 1000 000.

[EspenH]

Hi, I belive I found a solution. The issue was that I had to drill down to combinations of dimensions which did not exist (thanks to Marcus_sommer which pointed me in the right direction!) In example I had transactions in one time periode (year/month) dimension but not the other (in combination with four other dimensions). This became very clear on the lowest level of the drill down.

One solution was to use Cartesian product to create missing values, but due to a large fact table, a complicated data model and too many dimensions i skipped that ide.

Instead i created a smal time table with distinct year/month from the fact table and used this as my time dimension (we do not need to drill down below month - but its expandable). I then created variables for max year, max year-1 and month. In use we choose one year and possibly some months and want to compare this with the year before. We used these variables in the in the set expressions in our master measures. The master measures are used with aggr function to cover all our dimensions in the drill down. 

My testing so far show that we are now able to compare all periodes with all the dimensions even those we do not have any transactions.