topic Re: Default value in text box in QlikView

Default value in text box

arethaking — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

I have the data like this on monthly level.

Product	Amount	Month Name	Month	Year	Status
P1	7	Aug	Aug-16	2016	A
P1	7	Sep	Sep-16	2016	A
P1	10	Jan	Jan-15	2015	A
P1	10	Feb	Feb-15	2015	A
P1	10	Apr	Apr-15	2015	A
P1	10	Jan	Jan-16	2016	A
P1	10	Feb	Feb-16	2016	A
P1	10	Mar	Mar-16	2016	A
P1	10	Apr	Apr-16	2016	A
P1	10	May	May-16	2016	A

By default , I want to show Amount value for the latest month in a text box . It should change the value based on month,Year,Product selection.

I am using the below formula. But it is not working.Please help me.

=SUM({<Year={'$(=Max(Year))'},Month={'$(=Max(Month))'},Status={'A'}>} Amount)

Re: Default value in text box

marcus_sommer — Mon, 05 Sep 2016 08:50:26 GMT

You need for this a numeric month-fields and not a string-field.

- Marcus

Re: Default value in text box

Anonymous — Mon, 05 Sep 2016 08:51:12 GMT

This part would not work here Month={'$(=Max(Month))

Max() would return integer value and your month is having value like Aug-16 etc.

Then how it will work?

Workaround like, create a inline function like

Load * inline

[MonthName, MonthNo

Jan, 1

Feb,2

and so on..

];

Then map this table into your table using applymap()

and use MonthNo={'$(=Max(MonthNo))

Re: Default value in text box

Anonymous — Mon, 05 Sep 2016 08:52:41 GMT

Check this as well?

Comparing Current Month and Previous Month Sales

Re: Default value in text box

antoniotiman — Mon, 05 Sep 2016 09:38:35 GMT

Hi Aretha,

in Your Doc [MonthName] is Date/Dual Field, so try

=SUM({<MonthName={'$(=Max(MonthName))'},Status={'A'}>} Amount)

Regards,

Antonio

Re: Default value in text box

Anonymous — Mon, 05 Sep 2016 09:53:56 GMT

Hi Aretha,

Think that max(month) is ever 12, then, you have to calculate the max month of the max year.

=SUM({<Year={'$(=Max(Year))'},Month={'$(=Max({$(=Max(Year))}Month))'},Status={'A'}>} Amount)

Regards!!

Re: Default value in text box

tamilarasu — Mon, 05 Sep 2016 10:31:55 GMT

Hi Aretha,

Change your load your script like below and check.

sales:

LOAD Amount,
Product,
Date(date#(Year,'YYYY'),'YYYY') as Year,
Date(date#([Month Name],'MMM'),'MMM') as Month,
Date(date#([Month Name] &'-'& right(Year,2),'MMM-YY'),'MMM-YY') as MonthName
FROM
[Sales Data.xls]
(biff, embedded labels, table is Sheet1$);

Re: Default value in text box

Anil_Babu_Samineni — Mon, 05 Sep 2016 10:48:11 GMT

Try Static, They look Dynamic

Static

Sum({<Year = {'2016'}, Month={'Dec'}, Status={'A'}>}Amount)

If this expression give any value then look script, Is that table is Simple excel table or have you used any other functionalities.

- ANIL

Re: Default value in text box

Anonymous — Thu, 03 May 2018 09:59:15 GMT

Hi Aretha,

First change the script as:

LOAD * INLINE [
MonthID, Month
1, Jan
2, Feb
3, Mar
4, Apr
5, May
6, Jun
7, Jul
8, Aug
9, Sep
10, Oct
11, Nov
12, Dec
];

Use this formula:

=SUM({<CYear={$(=Max(CYear))},Month={$(=Max(MonthID))}>}ExtInvMisc)

My year field is CYear and Month field is Month. So for your data use Month Name and Year.

Thanks