topic Re: show data for latest month in chart qliksense in App Development

show data for latest month in chart qliksense

Aspiring_Developer — Mon, 25 Jul 2022 09:26:26 GMT

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

Re: show data for latest month in chart qliksense

edwin — Mon, 25 Jul 2022 11:11:50 GMT

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

Re: show data for latest month in chart qliksense

Aspiring_Developer — Mon, 25 Jul 2022 11:23:58 GMT

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

Re: show data for latest month in chart qliksense

Aspiring_Developer — Thu, 28 Jul 2022 13:48:18 GMT

Hello ,

Can anyone please help on this ?

Thanks

Re: show data for latest month in chart qliksense

edwin — Thu, 28 Jul 2022 17:14:51 GMT

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

Re: show data for latest month in chart qliksense

Aspiring_Developer — Fri, 29 Jul 2022 08:16:22 GMT

Hi @edwin

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