topic Re: Expressions values to store in variable in QlikView

Expressions values to store in variable

Mon, 15 Sep 2014 12:57:32 GMT

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.

Re: Expressions values to store in variable

MK_QSL — Mon, 15 Sep 2014 13:00:48 GMT

Can you please elaborate little more?

Re: Expressions values to store in variable

Mon, 15 Sep 2014 13:38:20 GMT

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.

Re: Expressions values to store in variable

anbu1984 — Mon, 15 Sep 2014 13:42:43 GMT

vMth - Month expression

vYr - Year expression

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

Re: Expressions values to store in variable

MK_QSL — Mon, 15 Sep 2014 13:58:46 GMT

Create Two Variables

vMonth

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.

Re: Expressions values to store in variable

Mon, 15 Sep 2014 14:21:57 GMT

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 %')

Re: Expressions values to store in variable

MK_QSL — Mon, 15 Sep 2014 14:25:18 GMT

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

Re: Expressions values to store in variable

Mon, 15 Sep 2014 14:27:52 GMT

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 %')

Re: Expressions values to store in variable

MK_QSL — Mon, 15 Sep 2014 14:34:49 GMT

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....

Re: Expressions values to store in variable

Mon, 15 Sep 2014 14:38:23 GMT

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 %')

Re: Expressions values to store in variable

MK_QSL — Mon, 15 Sep 2014 14:48:39 GMT

In your expression, Year is remaining same, so it will give you result like

Jan 2014, Dec 2014, Nov 2014, Oct 2014.... Correct me if I am wrong...

Re: Expressions values to store in variable

Mon, 15 Sep 2014 15:25:22 GMT

Hi Manish,

It seems Year will be wrong. I didn't think about year changes. the correct way is Jan 2014, Dec 2013, Nov 2013 and Oct 2013.

How to achive it? I am very new to this. pls help.

Thanks,

Lakshmi.

Re: Expressions values to store in variable

Mon, 15 Sep 2014 15:47:35 GMT

Hi Anbu,

can you pls help?

It seems Year will be wrong. I didn't think about year changes. the correct way is Jan 2014, Dec 2013, Nov 2013 and Oct 2013.

How to achive it? I am very new to this. pls help.

Thanks,

Lakshmi.