topic Re: Exclude last date in dimension in QlikView

Exclude last date in dimension

jlampard40 — Sat, 16 Nov 2024 01:51:00 GMT

Hi - I have the following expression:

=Count({<Activity_or_Demand={'Demand'}>}Event_Key)

and using dimension called 'Financial_Year_Week'

This will show nicely a count of event keys per financial year week. However, as this data is updated on a daily basis, I want to exclude the latest week as it will only be partial data until the following week begins. How can I modify my expression to exclude the latest financial year week? In this case (at the moment) I will want to exclude 'FY19/20 34' i.e. week 34 of FY 19/20. However, I don't want to keep having to change the expression! Would be nice to have a script which simply excludes the latest (MAX?) financial_year_week on a weekly basis.

Many thanks guys!

Rgds

Re: Exclude last date in dimension

Anil_Babu_Samineni — Tue, 26 Nov 2019 12:57:31 GMT

1) You want to exclude the Max week of data in the entire model

2) Same for Max Financial Year data also?

Re: Exclude last date in dimension

jlampard40 — Tue, 26 Nov 2019 13:01:45 GMT

I just want to exclude the MAX week in relation to the field 'Financial_Year_Week'.

Rgds

Re: Exclude last date in dimension

Brett_Bleess — Mon, 09 Dec 2019 22:01:24 GMT

Best I have to try to help you move things forward is a Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Excluding-values-in-Set-Analysis/ba-p/1471704

Here is another related to dates in set analysis:

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

Hopefully between those two, they may help you get things sorted until one of the more brilliant folks out here other than me can have a further look for you.

Regards,
Brett

Re: Exclude last date in dimension

qliksus — Wed, 11 Dec 2019 06:53:02 GMT

If you want to exclude just one week you can try the below

=Count({<Activity_or_Demand={'Demand'} , Week=-{'$(=max({<year={"=max(year)"}>}week))'}>}Event_Key)