Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Disappearing Data, when I click a date

Dear All

I'll try and set the scene

Query Picture One.PNG

Query 2 From The CCV Reason Table.PNGQuery 3 from the Fact Table.PNG

BEFORE SELECTION

query4.PNG

AFTER SELECTION

query5.PNG

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?

1 Solution

Accepted Solutions
Highlighted
Not applicable

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.

View solution in original post

9 Replies
Highlighted
Master
Master

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.

Highlighted
Not applicable

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.

Highlighted
Master
Master

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?

Highlighted
Champion III
Champion III

A '{' is missing

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Not applicable

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.

Highlighted
Not applicable

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.

Highlighted
Not applicable

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.

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

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