use Lookup to find value ?

[QFanatic]

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

[sunny_talwar · Accepted solution]

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))

[QFanatic]

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]

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