Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am very new to Qlik Sense and I'm still trying to understand how data connections work. I am trying to create a table that will list all the products that have no sales for more than 3 months and I used calculated dimension expression as below to find them:
IF(AGGR(MAX(date), ProductCode, ProductName) < ADDMONTHS(Today(),-3), ProductCode & ' / ' & ProductName)
I wanted to cross check the values with other tables, thus, I tried making selections directly from the table with the expression above to filter all the tables. I realized that some rows in the table can correctly identify the respective product code and product name from the calculated dimension and some rows don't upon selecting them. For example, I clicked on '1234/Product A' and Qlik Sense automatically made the selection: Product Code: 1234 and Product Name: Product A, however for '2333/Product B', the selections will not be made. Can anyone explain what is causing this to happen?
Your data and/or data-model might not suitable for the wanted views. Very important is the understanding that NULL isn't stored in any way and couldn't be accessed/selected - at least not directly. This means if there are any records it could be quite difficult to impossible to show within charts that there are no data. Before struggling in the UI to enforce the match of calculated dimensions from a dimension-table with the not existing fact-records it's often more expedient to populate the missing facts.
Another aspect is your mentioned attempt to link your selections against n tables. Such approach will only be working if all key-values are existing within all related tables. If not - anything will be orphaned and not shown. The missing/wrong key-values might be checked and populated but simpler would be to skip the linking by merging everything related within a single fact-table and designing the data-model as star-scheme.
Beside the above general recommendations it could in use-cases like your be helpful to aggregate the min/max dates in the script and/or to calculate the date-offset against today() and/or the offsets against each other on a record-level - with interrecord-functions like peek() and previous - and a lot more flag/grouping/cluster stuff which won't bypass the above hinted challenges but could enable different views to show the same information in another way.