Current Month date data

[inam]

Hello Community,

i am taking 4 expression for calculate last 4 date data. see below code . and i have raw data last month to today-1 (1st july to 17 aug)

=if(day(Today())=1,
Num((Count({< Floor_DC = {"=floor(Date(MonthEnd(Now()-1)-3,'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 4)))))) <=$(=(floor(Date(Now()-4))))"}>} PROD_TYPE)),'##'))

=if(day(Today())=1,
Num((Count({< Floor_DC = {"=floor(Date(MonthEnd(Now()-1)-2,'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 3)))))) <=$(=(floor(Date(Now()-3))))"}>} PROD_TYPE)),'##'))

=if(day(today())=1,
Num((Count({< Floor_DC = {"=floor(Date(MonthEnd(Now()-1)-1,'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 2)))))) <=$(=(floor(Date(Now()-2))))"}>} PROD_TYPE)),'##'))

=if(day(today())=1,
Num((Count({< Floor_DC = {"=floor(date(MonthEnd(Now()-1),'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 1)))))) <=$(=(floor(Date(Now()-1))))"}>} PROD_TYPE)),'##'))

and its working fine if the date is 1st august is show last 4 date data.

but i want if the date is 2 august is show only current month date data other colum shows '0' see below example

If TODAY DATE IS 2 AUGUST
c product	29-07-2023	30-07-2023	31-07-2023	01-08-2023
a	0	0	0	5
b	0	0	0	10
c	0	0	0	6

If TODAY DATE IS 3 AUGUST
c product	30-07-2023	31-07-2023	01-07-2023	02-08-2023
a	0	0	5	115
b	0	0	10	101
c	0	0	6	65

If TODAY DATE IS 4 AUGUST
c product	31-07-2023	01-07-2023	02-07-2023	03-08-2023
a	0	5	115	45
b	0	10	101	69
c	0	6	65	70

how can i handle this in where condition (script level)? &  if not so how can i handle in expression?

[pedrohenriqueperna]

In script you could try something like this:

Temp:

LOAD *,

If(<your date field>  >= Date(Today() - 4, 'DD-MM-YYYY'), <your aggregation expression>, 0) as yourcalculatedfield

From [<your field path>]

[inam]

Hello @pedrohenriqueperna , Thanks For Reply

i am using your code but not getting output .

i want if date is 1 show last 4 days value 

if date is 2,3,4 last 4 date value but only current month 

for example :-

if today date 1st august show last 4 date data july 28,29,30,31 all values

if today date is 2 august  show last 4 date data 29,30,31,1 but here show only 1st august data i want other column 0       

if date is 3 august show last 4 date data 30,31,1,2 so only show 1&2 data  i want other column (30,31) 0

if date is 4 august show last 4 date data  31,1,2,3 so only show 1,2&3 data i want other column (31) 0

if date is 5 august show last 4 days data 1,2,3,4 all value 

[pedrohenriqueperna]

Are you able to share a .qvf sample?

[inam]

Hello @pedrohenriqueperna Thanks for reply ,

Please Find Below Test qvw. 

Expected Output:-

If TODAY DATE IS 1 AUGUST
c product	28-07-2023	29-07-2023	30-07-2023	31-07-2023
a	0	34	78	0
b	98	67	32	56
c	45	34	12	114

till here I change my system date if date is 1st august code working fine show last 4 date data and value. 

but if date if date is 2 i want only current month  data see below example

If TODAY DATE IS 2 AUGUST
c product	29-07-2023	30-07-2023	31-07-2023	01-08-2023
a	0	0	0	5
b	0	0	0	10
c	0	0	0	6

If TODAY DATE IS 3 AUGUST
c product	30-07-2023	31-07-2023	01-08-2023	02-08-2023
a	0	0	5	115
b	0	0	10	101
c	0	0	6	65

If TODAY DATE IS 4 AUGUST
c product	31-07-2023	01-08-2023	02-08-2023	03-08-2023
a	0	5	115	45
b	0	10	101	69
c	0	6	65	70

If TODAY DATE IS 5 AUGUST
c product	01-08-2023	02-08-2023	03-08-2023	04-08-2023
a	5	115	45	455
b	10	101	69	89
c	6	65	70	91

[pedrohenriqueperna]

Hello,

I'm not an expert, but I think the file is for Qlik View and I use SaaS Qlik Sense, but I loaded the data inline to test and I think I got it to work. I'm not sure if you have a master calendar or something, or even other fields that would affect (and should be placed in the set analysis) the following expression did the job as far as my data goes:

If(GetSelectedCount(date) = 0,

//THEN
Sum(value),

//ELSE
sum({<date = >} If(date >= '$(=Max(date)-4)' and date >= '$(=MonthStart(Max(GetFieldSelections(date))))' and date <= '$(=Max(GetFieldSelections(date)))' , value))

)

In case there's no selection in the date field, the table will show every result. If there's a selection the expression will evaluate if the date of the field is -5 days range and lower than the maximum selected date. Note that I used a pivot table giving the table structure you provided, where the dates come as a column dimension.

This is the loaded data in case you need:

Temp:
load * inline [
c product, date, value
a, 2023-07-22, 66
a, 2023-07-23, 43
a, 2023-07-24, 21
a, 2023-07-25, 75
a, 2023-07-26, 88
a, 2023-07-27, 49
a, 2023-07-28, 91
a, 2023-07-29, 9
a, 2023-07-30, 15
a, 2023-07-31, 20
a, 2023-08-01, 25
a, 2023-08-02, 30
a, 2023-08-03, 41
a, 2023-08-04, 66
a, 2023-08-05, 78
a, 2023-08-06, 99
a, 2023-08-07, 112
b, 2023-07-22, 22
b, 2023-07-23, 13
b, 2023-07-24, 16
b, 2023-07-25, 19
b, 2023-07-26, 24
b, 2023-07-27, 19
b, 2023-07-28, 12
b, 2023-07-29, 18
b, 2023-07-30, 33
b, 2023-07-31, 110
b, 2023-08-01, 233
b, 2023-08-02, 88
b, 2023-08-03, 123
b, 2023-08-04, 76
b, 2023-08-05, 90
b, 2023-08-06, 33
b, 2023-08-07, 45
c, 2023-07-22, 66
c, 2023-07-23, 43
c, 2023-07-24, 21
c, 2023-07-25, 75
c, 2023-07-26, 88
c, 2023-07-27, 49
c, 2023-07-28, 3
c, 2023-07-29, 8
c, 2023-07-30, 78
c, 2023-07-31, 13
c, 2023-08-01, 43
c, 2023-08-02, 9
c, 2023-08-03, 13
c, 2023-08-04, 64
c, 2023-08-05, 66
c, 2023-08-06, 82
c, 2023-08-07, 85
];