Look at selected month and month - 1

[whiteside]

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:

YearMonth	Year.Month	Month
2018-03	2018.03	Mar
2018-04	2018.04	Apr
2018-05	2018.05	May
2018-06	2018.06	Jun
2018-07	2018.07	Jul
2018-08	2018.08	Aug
2018-09	2018.09	Sep
2018-10	2018.10	Oct
2018-11	2018.11	Nov
2018-12	2018.12	Dec
2019-01	2019.01	Jan
2019-02	2019.02	Feb
2019-03	2019.03	Mar

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

[Channa]

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

[whiteside]

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]

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

[whiteside]

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.

YearMonth	Year.Month	Month	Amount
2018-03	2018.03	Mar	2053
2018-04	2018.04	Apr	91
2018-05	2018.05	May	497
2018-06	2018.06	Jun	726
2018-07	2018.07	Jul	474
2018-08	2018.08	Aug	4134
2018-09	2018.09	Sep	762
2018-10	2018.10	Oct	1292
2018-11	2018.11	Nov	196
2018-12	2018.12	Dec	512
2019-01	2019.01	Jan	5309
2019-02	2019.02	Feb	2661
2019-03	2019.03	Mar	24

[Channa]

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

[whiteside]

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