Expressions values to store in variable

[#1]

Hi All,

I need help...

I have a table with 2 fields – Count1, Date1.

I have 2 expressions which will give me Month & Year based on some other logic. Through this Month & Year I need to show in my text box as below:

Sum of monthly Count1 / Sum of Total records Count1 * 100 & ‘%’

Here is my expressions to get Month:

=IF(GetSelectedCount([Date]) > 0 ,Month([Date]),
IF(GetSelectedCount(Year) = 0 and GetSelectedCount(Month) = 0, Month(Today()),
IF(GetSelectedCount(Year) > 0 and GetSelectedCount(Year) = Year(Today()), Month(Today()),
IF(GetSelectedCount(Year) > 0 and GetSelectedCount(Year) < Year(Today()), SubField('$(MonthNames)',';',Max(Month))))))

To Get Year:

=IF(GetSelectedCount([Date]) > 0 ,Year([Date]),
IF(GetFieldSelections(Year)<Year(Today()),Year,Year(Today())))

Thanks,

Lakshmi.

[MK_QSL]

Can you please elaborate little more?

[#3]

Hi Manish,

I have a table with 2 fields - Count1 & Date1.

I have to get total of Count1 by month & Year where as month & year = above expressions.

Thanks,

Lakshmi.

[anbu1984]

vMth - Month expression

vYr - Year expression

=count({<Month={$(vMth)},Year={$(vYr)}>}Field1)

[MK_QSL]

Create Two Variables

vMonth

=IF(GetSelectedCount([Date]) > 0 ,Month([Date]),
IF(GetSelectedCount(Year) = 0 and GetSelectedCount(Month) = 0, Month(Today()),
IF(GetSelectedCount(Year) > 0 and GetSelectedCount(Year) = Year(Today()), Month(Today()),
IF(GetSelectedCount(Year) > 0 and GetSelectedCount(Year) < Year(Today()),SubField('$(MonthNames)',';',Max(Month))))))

vYear

=IF(GetSelectedCount([Date]) > 0 ,Year([Date]),
IF(GetFieldSelections(Year)<Year(Today()),Year,Year(Today())))

SUM({<Month = {'$(vMonth)'}, Year = {'$(vYear)'}>}Count1)/SUM(TOTAL {<Month = {'$(vMonth)'}, Year = {'$(vYear)'}>}Count1)

Try vMonth and vYear both way... i.e. with = from expression start and without = also.

[#6]

Hi Anbu & Manish,

Thanks a lot

Seems it is working, I have to display the data for another previous 3 months as well.

.e.g. I choose DEC. I needs to show totals in 4 text boxes for Sept, Oct, Nov, Dec (this is done).

Do I needs to create 4 different Month variables and can I do -1 to get the previous total?

Is it correct?

=num(TextCount({<Month = {'$_1(vMonth)'}, Year = {'$(vYear)'}>}Topic) / count(count1) , '#.00 %')

[MK_QSL]

You can do it both ways... Either create six variable or create 2 only and change accordingly in Set Analysis

[#8]

Hi Manish,

is this correct for previous 3 months?

=num(TextCount({<Month = {'$_1(vMonth)'}, Year = {'$(vYear)'}>}Topic) / count(count1) , '#.00 %')

=num(TextCount({<Month = {'$_2(vMonth)'}, Year = {'$(vYear)'}>}Topic) / count(count1) , '#.00 %')

=num(TextCount({<Month = {'$_3(vMonth)'}, Year = {'$(vYear)'}>}Topic) / count(count1) , '#.00 %')

[MK_QSL]

Current Month

=SubField('$(MonthNames)',';',IF(GetSelectedCount([Invoice Date]) > 0 ,Month([Invoice Date]),

IF(GetSelectedCount(Year) = 0 and GetSelectedCount(Month) = 0, Month(Today()),

IF(GetSelectedCount(Year) > 0 and GetSelectedCount(Year) = Year(Today()), Month(Today()),

IF(GetSelectedCount(Year) > 0 and GetSelectedCount(Year) < Year(Today()),Max(Month))))))

Last Month

=SubField('$(MonthNames)',';',IF(GetSelectedCount([Invoice Date]) > 0 ,Month([Invoice Date]),

IF(GetSelectedCount(Year) = 0 and GetSelectedCount(Month) = 0, Month(Today()),

IF(GetSelectedCount(Year) > 0 and GetSelectedCount(Year) = Year(Today()), Month(Today()),

IF(GetSelectedCount(Year) > 0 and GetSelectedCount(Year) < Year(Today()),Max(Month)))))-1)

2nd Last Month

=SubField('$(MonthNames)',';',IF(GetSelectedCount([Invoice Date]) > 0 ,Month([Invoice Date]),

IF(GetSelectedCount(Year) = 0 and GetSelectedCount(Month) = 0, Month(Today()),

IF(GetSelectedCount(Year) > 0 and GetSelectedCount(Year) = Year(Today()), Month(Today()),

IF(GetSelectedCount(Year) > 0 and GetSelectedCount(Year) < Year(Today()),Max(Month)))))-2)

But you need make sure that this would not be enough to give you answer... Let me clarify bit more...

Consider that you have selected Year 2014 and Month Jan...

Now you want the result for Jan 2014, Dec 2013 and Nov 2013....

[#10]

Hi Manish,

Yes, you assumption is correct. Needs to display results as Jan 2014, Dec 2013, Nov 2013, Oct 2013.

Hence Can I do this way for 4 months?

=num(TextCount({<Month = {'$(vMonth)'}, Year = {'$(vYear)'}>}Topic) / count(count1) , '#.00 %')

=num(TextCount({<Month = {'$_1(vMonth)'}, Year = {'$(vYear)'}>}Topic) / count(count1) , '#.00 %')

=num(TextCount({<Month = {'$_2(vMonth)'}, Year = {'$(vYear)'}>}Topic) / count(count1) , '#.00 %')

=num(TextCount({<Month = {'$_3(vMonth)'}, Year = {'$(vYear)'}>}Topic) / count(count1) , '#.00 %')