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: 
whiteside
Contributor III
Contributor III

Look at selected month and month - 1

Hello Everyone,

I am in a bit of a pickle here.

"count({<[Status]={'In Time'}>}distinct [Shipments])/count(distinct [Shipments])"

 This formula gives me the quota of all in time shipmtes of all time.

Sometimes I set a filter to a specific month which I use the following fields for:

YearMonthYear.MonthMonth
2018-032018.03Mar
2018-042018.04Apr
2018-052018.05May
2018-062018.06Jun
2018-072018.07Jul
2018-082018.08Aug
2018-092018.09Sep
2018-102018.10Oct
2018-112018.11Nov
2018-122018.12Dec
2019-012019.01Jan
2019-022019.02Feb
2019-032019.03Mar

Then the Quota changes for that specific month.

Anyways, we tried to include some syntax in a second KPI which is a copy of the original formula but includes like Month - 1 or Year.Month - 1, but the quota always stays empty.

This makes sense because if I make a filter in the whole app for the month off May, clearly the formula finds no data from the previous month.

 Anyhow, is there a way to make this formula work? So when I set the App Filter to May my second KPI kind of ignores that filter but still realizes that I would like to look at April?

Best regards

whiteside

6 Replies
Channa
Specialist III
Specialist III

count({$<[MonthYear]={"$(=Date(AddMonths(Max(MonthYear), -1), 'MMM-YYYY'))"}>}[shipment])

Channa
whiteside
Contributor III
Contributor III
Author

Hi, i don't have the field MonthYear. But I have the Field Month.Year which has values like 11.2018. So i changed your sintax to count({$<[Month.Year]={"$(=Date(AddMonths(Max(Month.Year), -1), 'MM.YYYY'))"}>}[Shipment no]) but it didnt work.

Channa
Specialist III
Specialist III

Brother try this

 

=sum({$<YearMonth=, year_month={"$(=Date(AddMonths(date(date#(year_month, 'YYYY.MM'),'YYYY.MM'), -1), 'YYYY.MM'))"}>}sal)

 

check QVF also

 

i added filter in APP if you select if you select 2018-08 in KPI you will see 500 which is for 2018-07

Channa
whiteside
Contributor III
Contributor III
Author

Hey brother, thank you so much for the fast reply.

To be honest I tried your formular for a while now and I can't get it to work. Maybe I didn't explain myself correctly. Maybe it is not even possible.

If I set a global filter in the app itself, lets say for year-month 2018-04

Then I have one KPI in the sheet with the Measurement "Amount" and it displays 91.

Some how I want a second KPI in the same sheet that displays the Amount 2053 (from the previous month)

Maybe your previous answer was correct and i simply can't get it to work in my app.

YearMonthYear.MonthMonthAmount
2018-032018.03Mar2053
2018-042018.04Apr91
2018-052018.05May497
2018-062018.06Jun726
2018-072018.07Jul474
2018-082018.08Aug4134
2018-092018.09Sep762
2018-102018.10Oct1292
2018-112018.11Nov196
2018-122018.12Dec512
2019-012019.01Jan5309
2019-022019.02Feb2661
2019-032019.03Mar24
Channa
Specialist III
Specialist III

hear i have added qvf it has two KPI's one is for the selected month which can be current month

another one is for previous month

Channa
whiteside
Contributor III
Contributor III
Author

oh, i ddint see that file. I will try it your. thanks.