topic Show information until the present month in QlikView

Show information until the present month

Mon, 21 Mar 2011 13:10:19 GMT

Hello togehter, i have the following question, i hope its understandable.

I have a Pivot Table that compares budget with real movements.

I load the complete budget from an own company´s system to the month of July of 2011

I need to show the information until the present month (March 2011)

Can you help me?

Thanks and regards!

Show information until the present month

Oleg_Troyansky — Mon, 21 Mar 2011 13:39:43 GMT

One of the simplest solutions would be to add a calculated "flag" field in your Calendar table, something like "YTD_Flag" - for all dates until the present Month, the flag = 1 and for all other dates, Flag = 0.

Then, in your expression, you can do one of the two:

1. Multiply by the flag: sum(Budget*YTD_Flag) - only those dates with Flag=1 will be included,

2. Or, if you like Set Analysis, you can use the flag in Set Analysis: sum( {<YTDFlag = {1}>} Budget)

cheers,

Show information until the present month

Mon, 21 Mar 2011 13:57:46 GMT

Thanks Oleg for your answer. I don´t have a calendar table...

I understand your propose solution, but you can suggest another solution?

Thanks

Show information until the present month

Mon, 21 Mar 2011 14:15:40 GMT

There are so many different ways to do it and I'm not sure which is the "best" one but I would just make a variable (we'll call it vMonthStart)for the first date you don't want, assuming you want March 2011 to be the last month I would use something like...

=monthstart(addmonths(today(),1))

For dimension I would use...

=dual(month([Date]) & ' ' & year([Date]),Makedate(year([Date]),month([Date]),1))

Expression:

=sum({$<[Date]={"<$(vMonthStart)"}>} [Budget])

Off the top of my head I don't know if the syntax and everything is perfect above but that's the general idea. I'm assuming you don't want to use the script (since you didn't like the solution above this post) so you wouldn't have to with that solution but I'm sure somebody smarter than me will come in here and give you a better solution.

Hopefully that helps.

Show information until the present month

Mon, 21 Mar 2011 15:20:47 GMT

The load of the budget cannot be limited so that it does not bring the later months to the present one??

I tried in script limit the load with the function TODAY, but don´t work

Show information until the present month

Mon, 21 Mar 2011 15:28:23 GMT

You can add this:

WHERE [Your Date Field]<=today();

below your FROM statement.

Show information until the present month

Mon, 21 Mar 2011 15:58:13 GMT

Based in answer of Trent.Jones, i solved this problem.

Thanks to all!!!!