Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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?

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Disappearing Data, when I click a date

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.

9 Replies
rittermd
Honored Contributor

Re: Disappearing Data, when I click a date

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.

Not applicable

Re: Disappearing Data, when I click a date

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
Honored Contributor

Re: Disappearing Data, when I click a date

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?

mov
Esteemed Contributor III

Re: Disappearing Data, when I click a date

A '{' is missing

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

Re: Disappearing Data, when I click a date

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

Not applicable

Re: Disappearing Data, when I click a date

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.

Not applicable

Re: Disappearing Data, when I click a date

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.

Not applicable

Re: Disappearing Data, when I click a date

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.

Re: Disappearing Data, when I click a date

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