Disappearing Data, when I click a date

[#1]

Dear All

I'll try and set the scene

BEFORE SELECTION

AFTER SELECTION

Measure In Graph Is

sum({<TransactionType={'Month_CCV_Rands'},FinancialYear='$(v_Current_Financial_Year)'}>}Amount)

If I remove the v_Current_Financial_Year then data does appear.

This formula has worked my other views. It is strange that it has suddenly become fussy. What am I doing wrong? Why when I select something, not even related to date, does the information not show?

[rittermd]

Have you verified that the variable for Current Year contains what you expected it to?  I always create a text object just to see what is actually in the variable.

My guess is that it may not since when you remove it you do see data for this Reason.

Also, double check the syntax of your code.  The editor will some times say OK even when the code doesn't work.  It looks ok though.

[#3]

Hi Mark

Thanks for the response. I added a box on the top right and put in the variable and it shows 2017. So it does return a number and there is data in the 2017 year. The weirdest thing is that there is a an EXACT replica of the graphs and data for SALES in the previous page and it all works. It is probably something so small. I'll try a completely fresh app and see if it will work there.

[rittermd]

Qlik is so case sensitive.  Is it possible that there is not an exact match on the Reason?

Is the filter for reason coming directly from the data?

[Anonymous]

A '{' is missing

sum({<TransactionType={'Month_CCV_Rands'},FinancialYear={'$(v_Current_Financial_Year)'}>}Amount)

[rwunderlich]

I note that the subset ratio is less than 100% in both tables, which means you have some orphans in the fact table. Have you looked  into that?

-Rob

[#7]

Hi Michael

I don't know why I wrote it without the '{'. That bracket is in the calculation like so:

sum({<TransactionType={'Month_CCV_Rands'},FinancialYear={'$(v_Monthly_Current_Financial_Year)'}>}Amount)

I pressed the delete key to get it onto one line in the comment and that's when it disappeared.

[#8]

Hi Rob

Many thanks for the feedback. I have not looked into that. If I run these table in a SQL query they match and the data shows correctly. I think there will be some codes which don't have a match eg

CCV code reason is 15 in the reasons table, but in the transaction data, 15 has never been used.

[#9 · Accepted solution]

rittermd‌ Thanks Marc. Your idea played in my head.

I was about to send a sample of the data, deleting graphs and company related sensitive information, when I looked through the variables.

The variable $(v_Monthly_Current_Financial_Year) gets the last year by running the following:

=max({<TransactionType={'Month_Sales_Rands'}>}FinancialYear)

I thought that the variable would only give me a number eg 2018. But in fact it gives me a number, so long as the data that I'm looking at has a transactions called 'Month_Sales_Rands'. So the variable is not just a value

I went an created another variable call $(vMaxCCVFYear) and went ahead an change the criteria to get the last year to

=max({<TransactionType={'Month_CCV_Rands'}>}FinancialYear)

I get the same answer eg 2018. But because I am calculating the Maximum Financial Year based on data I am looking at. It works.

Frustrating error. Is this just Qlik? because if 1 + 1 = x and x is 2, surely I don't need to change the maths to 4 - 2 = x because I spoke about a 4.

[rwunderlich]

I follow your explanation, but I'm not understanding your questioning if this is an error. Your statement:

"But in fact it gives me a number, so long as the data that I'm looking at has a transactions called 'Month_Sales_Rands'. So the variable is not just a value"

makes sense to me.  Your variable is an expression, evaluated in the context of your selected data. If your selected data has no 'Month_CCV_Rands', then what set of FinancialYear values would be looked at to determine Max()?

If you want to ignore selections and consider all FinancialYears, then you can use the set identifier {1}.

-Rob