topic Re: Yearly Total for given Month in App Development

Yearly Total for given Month

shah_sapana — Wed, 27 Mar 2019 17:54:46 GMT

I am new to qlik sense and trying create one dashboard with YTD,MTD,QTD and WTD. My requirement is to pull data for entire year or quarter even if i filter data from one date. Like if i select 3/37/2019 than month chart should pull data for entire march and year chart should pull data for entire 2019 year. As of now it's pulling just for this particular date.

Please advise.

Thanks

Re: Yearly Total for given Month

dplr-rn — Wed, 27 Mar 2019 18:11:59 GMT

That is how it works by default.
One way to do what you want is set analysis where you specify month and year dynamically
e.g.
entire year - sum({<Date=,Year={'$(=Year(Max(Date)))'}>}LineSalesAmount)

similar approach for month
using max date here but you can use what suits your need

Re: Yearly Total for given Month

shah_sapana — Thu, 28 Mar 2019 14:19:24 GMT

Thanks and i really appreciate your prompt reply. But can you please elaborate your solution. As i mentioned i am new to Qlik.

what would be entire year ? and max date ? I mean i want to achieve sum of entire year or month by passing one date.

Like i said if i pass 03/28/2019 (transaction date) than my monthly chart should pull data for march (sum of revenue) and yearly chart should pull data for 2019.

Please suggest.

Re: Yearly Total for given Month

dplr-rn — Thu, 28 Mar 2019 14:33:15 GMT

Basically if you have a date field we tend to break down the date into different components to make selection easier.
for this we use master calendar table. this master calendar will have each value of transaction date and the corresponding day, month, year, month year etc.
e.g. for 03/28/2019 master calendar will have day-28, month-march, year 2019, month-year Mar-2019 etc.
this makes selections easier e.g. a user can select a filter called MonthYear and select every transaction from that year
check below (its qlikview but logic remains the same)
https://community.qlik.com/t5/QlikView-Scripting/Creating-A-Master-Calendar/td-p/341286

there is some inexpensive training courses on udemy i suggest you tackle that .

Re: Yearly Total for given Month

shah_sapana — Thu, 28 Mar 2019 15:25:34 GMT

I did that. I added Date Range Picker and it has year,month,week and date filters. But when i select date all my charts are getting filtered for those date range selection. I even tried to add that logic to expression of the measure.

Our client has this very specific requirement. If he selects transaction date than each chart should have respective data.

Here to see Year chart working i need to select that specific year. My visual has 4 charts and should show four different results.

I watched video you shared and will try to tweak my R script.

Thanks Again for all your help. If you find any specific solution to my question Please do share.

Re: Yearly Total for given Month

dplr-rn — Thu, 28 Mar 2019 15:44:33 GMT

No problem.
getting the data model right and using set analysis is the way to go
for the scenario you mentioned.

@shah_sapana wrote:
I did that. I added Date Range Picker and it has year,month,week and date filters. But when i select date all my charts are getting filtered for those date range selection. I even tried to add that logic to expression of the measure.

basically by default when a date is selected qlik narrows down the result to that date.

If use can select year that is best. or you can use set analysis like i mentioned.

Re: Yearly Total for given Month

shah_sapana — Thu, 28 Mar 2019 16:47:00 GMT

I am completely stuck. Set analysis is also not working. Configured for each chart as per requirement. If i select any specific date means it's pulling data just for that date i all of my charts.

I really wonder there should be some solution to it.

Re: Yearly Total for given Month

dplr-rn — Thu, 28 Mar 2019 16:48:24 GMT

Share a sample app/data set with examples of outputs needed.

Re: Yearly Total for given Month

shah_sapana — Thu, 28 Mar 2019 20:11:07 GMT

Please find attached sample visual. Other than that we have transaction date column in data set. Client is using excel and achieving same visual in Qlik is becoming challenge for me. It has three charts from yearly ,monthly and weekly. And upon selection all three charts show same data.

1) Is it doable in Qlik ?

2) How?

I tried calendar and master calendar both. In my date range picker i have all possible date related filters separate for each date elements.

Please suggest.