topic Re: how to change data in QlikView

how to change data

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

hi guys, i have a case,
when i filter date, i choose month , on november.
as we can see on the table, all data showed based on paymentdate.

Branch	InvoiceNo	InvoiceDate	PaymentDate	KEY_DATE	FLAG_TRANS	TERMSID	COD_GROUP	COD_INVOICE
CAA	CISI 18 1918 CAA	10/30/2018	11/8/2018	20181108	Duedate_Inv	1	>7 DAYS	9
CAA	CISI 18 1923 CAA	11/1/2018	11/2/2018	20181102	Duedate_Inv	1	1 DAYS	1
CAA	CISI 18 1927 CAA	11/1/2018	11/16/2018	20181116	Duedate_Inv	1	>7 DAYS	15
CAA	CISI 18 1954 CAA	11/9/2018	11/19/2018	20181119	Duedate_Inv	1	>7 DAYS	10

i use this :

count(aggr(sum(
{<
TERMSID={'1'},
FLAG_TRANS={'Duedate_Pelunasan_Inv'},
COD_GROUP={'>7 DAYS'},

Day={">=$(=Num(Min(Day)))<=$(=Max(Day))"},
// Month={'$(=max(Month))'},
Year={'$(=max(Year))'}
>} 
COD_INVOICE),[Invoice No]))

how to change based into invoicedate, so if i choose november, only invoicedate on november are show? because its still appear 1 data from october? did i miss something?

thank you.

Re: how to change data

prieper — Wed, 02 Jan 2019 11:15:37 GMT

The field "Day" is not in the above example.
Easiest would be to add a field with the month (and the year) for the invoice-date, make a selection on this field and skip the expressions after COD_Group:
count(aggr(sum(
{<
TERMSID={"1"},
FLAG_TRANS={"Duedate_Pelunasan_Inv"},
COD_GROUP={">7 DAYS"}
>}
COD_INVOICE), [Invoice No]))

it it advisable to place your search-expression in ", instead of '

Re: how to change data

davinfrost — Thu, 03 Jan 2019 02:46:07 GMT

when i removed "Day={">=$(=Num(Min(Day)))<=$(=Max(Day))"}," ,
it still incorrect, i mean , data from october which is "CISI 18 1918 CAA 10/30/2018",
is still appeared, what should i do??

Re: how to change data

prieper — Fri, 04 Jan 2019 10:32:14 GMT

Just post an example of your application please.
How do you calculate "Day" or "Month"?
If you need to have only those invoices with Payment-Date November and Invoice-Date November, you may create an island-field with the date to be selected and to which you refer in your calculation.

Re: how to change data

prieper — Fri, 04 Jan 2019 11:03:51 GMT

pfa and check, whether this helps.