Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QFanatic
Creator
Creator

use Lookup to find value ?

hi Everyone

PFA.

as you can see from the chart there is no data for Day 10 of April and May. 

I would like to insert Day 10 into the chart - with no data. Its much easier to see there's missing data for the 10th, in that way.

In my model, the Key field that joins the fact table to the Calendar, is TRAN_DATE.  To make it visible that the 10th is missing, I'd need to "manually insert" at least, a Zero volume row for that date.

I thought of looping through all the records in my table, looking up the 'NON Key' field TRAN2_DATE, and then do the 'forcing in' there.

I'm not sure if that idea would work - or if my Lookup statement here at all is 1) the right thing to do 2)actually does the right thing.

All input would be welcome thanks

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

Changing your expression to this seems to be helping

If(Sum({<PERIODID = {$(=Max(PERIODID)-2)}, Table = {"Table1"}>} TRN_AMOUNT) + Sum(0) = 0, 'N/A', Sum({<PERIODID = {$(=Max(PERIODID)-2)}, Table = {"Table1"}>} TRN_AMOUNT))

and

=If(Sum({<PERIODID = {$(=Max(PERIODID)-1)}, Table = {"Table1"}>} TRN_AMOUNT) + Sum(0) = 0, 'N/A', Sum({<PERIODID = {$(=Max(PERIODID)-1)}, Table = {"Table1"}>} TRN_AMOUNT))

image.png

View solution in original post

4 Replies
sunny_talwar

Changing your expression to this seems to be helping

If(Sum({<PERIODID = {$(=Max(PERIODID)-2)}, Table = {"Table1"}>} TRN_AMOUNT) + Sum(0) = 0, 'N/A', Sum({<PERIODID = {$(=Max(PERIODID)-2)}, Table = {"Table1"}>} TRN_AMOUNT))

and

=If(Sum({<PERIODID = {$(=Max(PERIODID)-1)}, Table = {"Table1"}>} TRN_AMOUNT) + Sum(0) = 0, 'N/A', Sum({<PERIODID = {$(=Max(PERIODID)-1)}, Table = {"Table1"}>} TRN_AMOUNT))

image.png

QFanatic
Creator
Creator
Author

Awesome as always, Thank you Sunny!

 

I have another model which has a similar problem, but this proposed solution, doesnt work there. I will create a new post for that and Tagg you.

 

Much appreciated

 

sunny_talwar

Sounds good

QFanatic
Creator
Creator
Author

hi Sunny

Here is the link of my new post - tried implementing your suggestion here as well - no luck 🙂

Much appreciated

 

https://community.qlik.com/t5/New-to-QlikView/Display-all-dates-of-Month/m-p/1709830#M390103