show data for latest month in chart qliksense

[Aspiring_Developer]

Hello All,

I have below table having dates till Dec 2022 . 

The Dates are coming till Dec 2022 due to master calendar. If I uncheck the options of suppressing zero values for add ons I get the below :-

 

As of now I have the data till April 2022 and my requirement is to show data for latest loaded month in this chart .

I tried using max date but my maximum month is dec 2022. 

I thought of using addmonths(max(date),-8) , it will give  me april but then it will be static. I wish to show the latest loade month in this chart.

Here is my master calendar script:-

Can anyone please help ?

Thanks in advance

[edwin]

since for the load, the max date does not change.  you should determine this in your load script.  get the max from your fact table and then add a flag when the date in your fact = max 

this way in your table you can just look for this flag to tell you the last loaded date.

alternatively, you can create a new field that has the max date - in case you need it.  for exampe if you want to show only the dates where <= max

with both approaches, you will have a varied options like creating avariable to hol dthe max date and in your set analysis {<DATEFIELD={"<=$(MAXDATEVARIABLE)"}>}  you of course need to be aware of teh date formats

[Aspiring_Developer]

Hi @edwin Thanks for your response .

Here is my data model :-

Here I have used linked table. Not sure what to do in the script.

This is my link table script :-

Could you please help ?

If we create a dummy field, how to do it ?

Thanks in advance

[Aspiring_Developer]

Hello ,

Can anyone please help on this ?

Thanks

[edwin]

you will have to test the script as i didnt try it in QLik Sense but this should give you an idea:

//this gets the max date (end of month) from your fact, creates a mapping table
maxDate:
mapping load date(max(monthend(Date))) as Date, 1 as IsMaxDate resident Link; 

//this adds a new flag that indicates if it is the month of last loaded month
noconcatenate
NewLink
load *, applymap('maxDate', monthend(Date), 0) as IsMaxDate resident maxDate;

drop table Link;

now in your expression, just add the set analysis {<IsMaxDate={1}>}

if instead you needed all data before and = to month loaded, it will be slightly diff

[Aspiring_Developer]

Hi @edwin 

Thanks for your response. I tried to test the field but it is giving me below error:-