topic Re: Set Analysis - Advanced Search in App Development

Set Analysis - Advanced Search

Ed — Fri, 02 Apr 2021 07:45:17 GMT

Hello,

I am facing quite an issue right now. I want to calcul the sum of my sales last year on the current month and fix this amout on my dashboard so that it doesn't move. The ultimate goal would be that I directly see this amout when I open my dashboard. I keep putting this formula upside down, I can't find the correct one 😞 Help

Sum({1<MyYear={"=$(=Year(Today())"} , MyMonth={"=$(=Month(Today()))"}}>} [Sales])

Also I tried to put a variable instead of Year(Today()) and Month(Today() but it still didn't work.
Also MyMonth and MyYear are the same type 1,2,3,...,12 for months and 2016,2017,2018 for years

Thanks

Re: Set Analysis - Advanced Search

sunny_talwar — Fri, 18 Jan 2019 13:36:15 GMT

Remove the = sign before the $ in your set modifier

Sum({1<MyYear = {"$(=Year(Today()))"}, MyMonth = {"$(=Month(Today()))"}>} [Sales])

Re: Set Analysis - Advanced Search

Ed — Fri, 18 Jan 2019 13:54:57 GMT

Hi,

Thanks for the quick response, unfortunately it doesn't work. It displays 0 for the measure in KPI visualization for this year. I should have 393. I would also like to have last year and not this year, where should I put the -1 ?

I don't calculate MyMonth and MyYear, the both fields comes from an excel sheet, they are allready like this

Re: Set Analysis - Advanced Search

sunny_talwar — Fri, 18 Jan 2019 13:45:07 GMT

Few questions

1) This gives you 0 for the current year?

Sum({1<MyYear = {"$(=Year(Today()))"}, MyMonth = {"$(=Month(Today()))"}>} [Sales])

2) How exactly do you calculate MyYear and MyMonth in the script? Can you share the script code used to create these fields?

3) It might be easier if you are able to share a sample to troubleshoot this better

Re: Set Analysis - Advanced Search

Ed — Fri, 18 Jan 2019 14:04:49 GMT

1) yes it gives me 0 for the current year and it shouldn't
2) I don't calculate them, I import them directly from excel sheet
Mois is MyMonth, Annee is MyYear and Nombre Intervention is sales
This is the load statement but my problem is within the measure I am trying to create
INTERVENTIONS :
LOAD
"Nombre Intervention",
Annee,
Mois
FROM [lib://Data/RequeteSQLMichel2.2.xlsx]
(ooxml, embedded labels, table is Feuil1) ;

Re: Set Analysis - Advanced Search

sunny_talwar — Fri, 18 Jan 2019 14:09:14 GMT

Can you check if one of the two below gives you a number other than 0?

Sum({1<Annee = {"$(=Year(Today()))"}>} [Nombre Intervention])

Sum({1<Mois = {"$(=Month(Today()))"}>} [Nombre Intervention])

My guess is that the first one might work, but the second one might not... but let's see what you get?

Re: Set Analysis - Advanced Search

Ed — Fri, 18 Jan 2019 14:14:57 GMT

You are right. The first line works but not the second one

Re: Set Analysis - Advanced Search

sunny_talwar — Fri, 18 Jan 2019 14:18:54 GMT

Can you share a filter object for Mois field? I want to see what format does it have? Is it Jan, Feb or is it 1, 2,... or is it Jan-2018, Feb-2018, ... etc

Re: Set Analysis - Advanced Search

Ed — Fri, 18 Jan 2019 14:23:49 GMT

Sure

Re: Set Analysis - Advanced Search

sunny_talwar — Fri, 18 Jan 2019 14:39:51 GMT

Try this

Current Year

Sum({1<Annee = {"$(=Year(Today()))"}, Mois = {"$(=Num(Month(Today())))"}>} [Nombre Intervention])

Previous Year

Sum({1<Annee = {"$(=Year(Today())-1)"}, Mois = {"$(=Num(Month(Today())))"}>} [Nombre Intervention])

Re: Set Analysis - Advanced Search

Ed — Fri, 18 Jan 2019 15:39:24 GMT

Yes, it's working ! Thanks a lot 🙂