exclude items variably based on actual date

[#1]

Hi,

I have a problem with chart expression (chart shows margin progress during last three years) - i want to create a chart which shows all data until the date as follows:

• IF(Day(Today())<15, show values until Month(Today())-1, (e.g. if today is 7.7.2015 exclude data booked after 31.5.2015)
• IF(Day(Today())>15, show values until Month(Today()), (e.g. if today is 7.7.2015 exclude data booked after 30.6.2015);

I can't do it by excluding month - it works but the QV excludes this month in every year. So I've created a variable

         =monthstart(Today()) hoping this excludes all data booked after 1st day of current month

but it is not working - maybe because of the date format? I adjusted the format like D.M.YYYY in script so it fits the format of variable result but still no progress. And how to use the variable in case I need to exclude also data book after 31.5.2015? Using (-1) do not give the right result.

Thank you.

Lucie

[sunny_talwar]

Try this may be:

=Date(MonthStart(Today()), 'D.M.YYYY')

[#3]

Thank you.

But this change the date format only but I need it to works in expression

=if(Day(Today())<15,sum({<Date={$(<(vDate-?))},Amount),

    if(Day(Today())>=15,sum({<Date={$(<(vDate))},Amount)))

where vDate represents the maximum date QV should include to amount calculation and which is not working. Any other ideas?

[sunny_talwar]

Not entirely sure what you are trying to do here. Regardless of which part of the if statement you are, you seem to be having the same expression (assuming {$(<(vDate-?))} to be a typo). Would you be able to provide with a sample qvw document or at least some sample data with expected output?

Best,

Sunny

[#5]

I would like to use something like vDate if (Day(Today())>15 (I want to sum only data before the first day of current month) and vDate-?(something like 1? not sure) if (Day(Today())<15 (I want to sum only data before the first day of previous month - as you can see above).

If I understand you correctly by changing the date format in variable I can influence the field value in expression?

So if my date format is DD.MM.YYYY and I'll change it in variable to MM-DD-YYYY then the QV won't be able to select all items after e.g. 11-13-2015 because the date in "Date" field looks like 13.11.2015?

Unfortunatelly I cant provide you by sample of qvw document...

Thanks, L.

[sunny_talwar]

Yes for set analysis to work on dates the Left Hand Side (LHS) and Right Hand Side (RHS) need to have same date format. Its annoying sometimes but that's the way it is.

So to make them same format, I would do something like this:

Sum({<Date={"$(='<' & Date($(vDate), 'DD.MM.YYYY'))"},Amount)))

Where Date is in the format DD.MM.YYYY

I hope this helps.

Best,

Sunny

[#7 · Accepted solution]

I solved it that way:

=if(Day(Today())<15,

    IF(sum({<Month=,Year=,Data.Date={'<$(vPriorMonthStart)'}, etc.

          where

               vPriorMonthStart=day(MonthStart(Today()))&'.'&(month(MonthStart(Today()))-1)&'.'&vCurrentYear

and

if(Day(Today())>=15,

    IF(sum({<Month=,Year=,Data.Date={'<$(vCurrentMonthStart)'}, etc.

          where

               vCurrentMonthStart=day(MonthStart(Today()))&'.'&num(month(MonthStart(Today())),'0')&'.'&vCurrentYear

Nothing else worked.

Thank you for help.

[sunny_talwar]

Awesome

I am glad you were able to figure it out. I would suggest closing the thread by marking your answer as 'Correct Answer', instead of 'Helpful Answer'.

Best,

Sunny