topic Re: Month To Date Customised in QlikView

Month To Date Customised

Wed, 11 Sep 2013 08:25:57 GMT

Hello!

I want to create a MonthToDate report that can show me the turnover like that:

day 1 day 2 .............. day 31 monthtodate(the sum of the other days) ....etc

How can i calculate the turnover for each day of the month and not lower the program's functionality with all the calculus implied? I have to mention that i have other objects on the sheet, and the formulas will be recalculated with every selection i make.

Thanks!

Re: Month To Date Customised

techvarun — Wed, 11 Sep 2013 08:53:59 GMT

see this

it may help

Re: Month To Date Customised

Wed, 11 Sep 2013 09:16:36 GMT

I'm not sure you understood what i need. "day 1", "day 2", etc each represents a column. Each column has its own expression area and i can't use a general formula for them. Which generates another question: can i dynamically create all those columns based on a general formula?

Re: Month To Date Customised

orital81 — Wed, 11 Sep 2013 09:31:06 GMT

Use a Pivot Table, set the date to be horizontal.

Your expression will be Month To Date value.

This way, you will get a column dynamically created for each day.

Re: Month To Date Customised

Wed, 11 Sep 2013 10:31:18 GMT

yes use pivot tables..it works

Re: Month To Date Customised

Wed, 11 Sep 2013 11:29:13 GMT

Yes, you are wight, it can work with a pivot. Now i have another problem. The first column will contain the days of the current month. By clicking the "+" in the day cell, all the information will refresh. The thing is that i can't import only the days i need, even though i wrote the formula in the Enable Condition field (Dimensions->Settings for Selected Dimension->Enable Condition). All i want is that the days are automatically be added, as time goes by. How can i make things right?

Re: Month To Date Customised

orital81 — Wed, 11 Sep 2013 11:38:21 GMT

Hi Laura

Please clarify: Which are the days you need and what condition you wrote to enable it?

It will be easier to help if you could attach your example, or at least screen shots.

Re: Re: Month To Date Customised

Wed, 11 Sep 2013 11:51:36 GMT

For the MTD for September, i want to have in the first column all the days, from the beginning of the month, up until now-11 September and i want the program to add dynamically each day in the chart. The condition i wrote is

=IF(AN=YEAR(TODAY()) AND LUNA=MONTH(TODAY()))

I want my table to have the structure as in the file i attached: date, group of fields, monthtodate, last year's monthtodate, difference, difference in % and a smiley face in the last column.

Re: Re: Month To Date Customised

orital81 — Wed, 11 Sep 2013 12:58:24 GMT

In order to show this MTD compared to last year MTD you can use this:

First, set variables:

LET vThisYear = Year(Today());

LET vLastYear =Year(Today())-1;

LET vThisMonth = Month(Today());

LET vLastMonth = Month(MonthStart(Today())-1);

LET vDayToday = Day(Today());

Make sure you have a calendar with Year, Month and Day

Now add calculations:

This MTD =

sum({$<Year = {$(vThisYear)},Month = {'$(vThisMonth)'}, DayDate = {"<=$(vDayToday)"}>} Total_Value)

Last MTD =

sum({$<Year = {$(vLastYear)},Month = {'$(vThisMonth)'}, DayDate = {"<=$(vDayToday)"}>} Total_Value)

Now, you don't need to add any condition in the dimension.

Let me know if that helped.

Ori

Re: Month To Date Customised

Wed, 11 Sep 2013 13:31:30 GMT

So you suggest tu use these formulas in the first column's expression (the one with the dates of the month)?

Re: Month To Date Customised

orital81 — Wed, 11 Sep 2013 14:18:57 GMT

No, in the 3rd and the 4th columns.

try to disable the first column, since it can have conflict with last year MTD.

You can just set it as Today().

Re: Month To Date Customised

Fri, 13 Sep 2013 05:53:40 GMT

For the 3rd and the 4th columns, i used inMonthToDate() function, and it worked just fine before adding the first column. Isn't ok if i leave those columns unaltered? I mean, can't i just change the first column to fit the others or do i have to adapt the others too?

Re: Re: Month To Date Customised

Thu, 19 Sep 2013 09:11:52 GMT

Hi Ori

excellent work this has helped me out alot, however I'm trying to modify it to calculate the YTD for a variable year and always using Today() date i.e. Month, day and year selected.

any help would be appreciated.

Thanks

David