Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create a table with Current Month YTD vs Previous Month YTD and it works unless I select a month because there is no previous month. Ultimately I would like the user to select a monthyear and the data will be ytd of this month. The following works if I have year selected, but I cannot seem to ignore selections when I select month.
My variables are as follows:
vMaxMonthYear=Date(max([MonthYear]), 'MMM-YYYY')
vPrevMonthYear=Date(addmonths(max([MonthYear]), -1), 'MMM-YYYY')
vMinMonthYear = =Date(min([MonthYear]), 'MMM-YYYY')
Previous Month Calc: =Sum({<[MonthYear]={'>$(=vMinMonthYear)<$(=vPrevMonthYear)'}>}PE.TOTAL_ACQ)
Current Month Calc: =Sum({<[MonthYear]={'>$(=vMinMonthYear)<$(=vMaxMonthYear)'}>}PE.TOTAL_ACQ)
I have a canonical date currently as list box, but I do not think this will work. I would liketo select a maxweekyear and have that auto populate other variables and calculations should work, just not sure how to go about doing this.
Any help would be greatly appreciated.
Thanks
Hi Brien,
Please see if this process I created helps you:
Regards,
Luis
Luis,
Thank you for this. I am not trying to build a ton of master calendars at this point, but this document is very helpful.
I guess I'm looking for a way to either:
- ignore selections in my set analysis statements, as I've been unsuccessful replacing the $ with a 1
- instead of providing a list box with Canonical date month, to provide a list box(or some other feature) to assign the vMaxMonthYear variable... haven't found the correct method yet.
Thanks for your input
Hi,
I guess I'd need to see what you're doing to help you better. I don't have QV but Qlik Sense.
If you want just to ignore selection of the YearMonth you could try this:
=Sum({<MonthYear=>} PE.TOTAL_ACQ)
Also, maybe if you show some screenshots of what you're trying to do, other members of the community could see it more easily and maybe provide you with ta better response
Cheers,
Luis
Hi,
Just wondering to know how did you go,
Cheers
Luis
When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others
I ended up going with a set analysis:
Sum({<Year={"$(=Max(Year))"}, MonthYear={"<=$(=date(Max(MonthYear),'MMM-YYYY'))"},Month=>}PE.TOTAL_ACQ)
This achieved my YTD filter with the list box being Month and Year