Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Help!!!
Hello I am new to Qlik.
I want to count all transactions which happened previous month with each customer.
For example, when I see the transaction data that happened at 2023-08 , I want one additional column to show how many transactions we had in 2023-07 with this customer.
So I made one column to show the current transaction month :
NUM(Month(Date#([REG_DATE],'YYYY-MM-DD')))
and then I made another column that show previous month using:
if(NUM(Month(Date#([REG_DATE], 'YYYY-MM-DD')))=1,12,NUM(Month(Date#([REG_DATE], 'YYYY-MM-DD')))-1)
Now I want to aggregate the transaction data that occurred previous month.
So I am trying to use code like this: Column(5) is the column of previous month.
Aggr(count(distinct if({<[NUM(Month(Date#([REG_DATE]],'YYYY-MM-DD')))]=Column(5)>},TRANSACTIONID)),CUSTOMER)
This does not work at all.
And I have no clue how I should fix this code.
Please help me.
I am not using script. I am using the sheet.
I don't want to go deep into the script.
could you share why a square bracket is being used?
Aggr(count(distinct if({<[NUM(Month(Date#([REG_DATE]],'YYYY-MM-DD')))]=Column(5)>},TRANSACTIONID)),CUSTOMER)
also the requirement for the if statement
No reason I just used it... I am newbie I just am trying in various ways...
from what I know, that is for fields. could you share example data (please remove any sensitive info) and a mockup of your desired output?
would suggest to calculate the month of REG_Date and the month prior of REG_DATE in the script and use that calculated field in the front.
have a look at: https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalys...
Hello Thank you for the answer but I changed my strategy to get the data.
I think I can count transaction happened in past 30days.
Now I am trying to use this code:
Aggr(count(distinct if( Date(REG_DATE)-30 <= REG_DATE <=Date(REG_DATE)),TRANSID),CUSTOMER)
Which does not work it comes with blank.
I would try to provide mock data if you prefer.